How to do this:

[Tim]

I need to format a range of cells, to be red if the cell
value is 1, blue if the cell value is 2 etc etc

I'll have to do this on differnet ranges, so I'm looking
for a way to apply the above formatting to whatever the
current users selection of cells is.

something like
range = current selection of cells
if cell in range = 1 then (format option)
if cell in range = 2 then (next format option

Something like that
TIA
Tim

 

[Bob Phillips]

Use conditional formatting with one condition of a value equal to red,
colour red, another condition value equal to 2, etc.

This only supports 3 conditions.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tim]

As you have said, only cover 3 conditions using
Conditional formatting - I need about 10.

to clarify - users selects a range of cells, each
containing a number
runs macro - checks the value of each cell in the range,
then gives it a colour depending on what the cell value
is.

TIA
tim

 

[Mike Fogleman]

Keep in mind that a max of 3 conditions can be used:

With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="1"
.FormatConditions(1).Interior.ColorIndex = 3 'red
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="2"
.FormatConditions(2).Interior.ColorIndex = 41 'blue
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="3"
.FormatConditions(3).Interior.ColorIndex = 4 'green
End With

Mike F

 

[Mike Fogleman]

Keep in mind that a max of 3 conditions can be used:

With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="1"
.FormatConditions(1).Interior.ColorIndex = 3 'red
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="2"
.FormatConditions(2).Interior.ColorIndex = 41 'blue
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="3"
.FormatConditions(3).Interior.ColorIndex = 4 'green
End With

Mike F

 

[Mike Fogleman]

Keep in mind that a max of 3 conditions can be used:

With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="1"
.FormatConditions(1).Interior.ColorIndex = 3 'red
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="2"
.FormatConditions(2).Interior.ColorIndex = 41 'blue
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="3"
.FormatConditions(3).Interior.ColorIndex = 4 'green
End With

Mike F

 

[Don Guillett]

modify one of these. The first one belongs in the SHEET code module.
right click on the sheet tab>view code>insert this>save

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
With Target.Interior
Select Case UCase(Target)
Case "ASK"
..ColorIndex = 4
Case "BASK"
..ColorIndex = 6
Case "BID"
..ColorIndex = 8
Case "BBID"
..ColorIndex = 44
Case "TRD"
..ColorIndex = 38
Case Else
..ColorIndex = 0
End Select
End With
End Sub

This one can be anywhere and assigned to a button.

Sub colorcells()
For Each c In Range("a1:a" & Cells(65536, 1).End(xlUp).Row)
With c.Interior
Select Case UCase(c)
Case "ASK"
..ColorIndex = 4
Case "BASK"
..ColorIndex = 6
Case "BID"
..ColorIndex = 8
Case "BBID"
..ColorIndex = 44
Case "TRD"
..ColorIndex = 38
Case Else
..ColorIndex = 0
End Select
End With
Next
End Sub

 

[Mike Fogleman]

Keep in mind that a max of 3 conditions can be used:

With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="1"
.FormatConditions(1).Interior.ColorIndex = 3 'red
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="2"
.FormatConditions(2).Interior.ColorIndex = 41 'blue
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="3"
.FormatConditions(3).Interior.ColorIndex = 4 'green
End With

Mike F

 

[Bob Phillips]

so why didn't you say?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

Have you learned nothing from the "King and I"?

value is 1, blue if the cell value is 2 etc etc

et cetera, et cetera, et cetera

(in my best Yul Brynner voice)

<gd&r>

 

[Bob Phillips]

Do you resemble him Dave?<g>

Bob

Have you learned nothing from the "King and I"?


et cetera, et cetera, et cetera

(in my best Yul Brynner voice)

<gd&r>

 

[Dave Peterson]

I still have my hair--getting more gray each day!