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

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" wrote in message
...
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

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

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
"Tim" wrote in message
...
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

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

"Tim" wrote in message
...
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

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
"Tim" wrote in message
...
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

modify one of these. The first one belongs in the SHEET code module.
right click on the sheet tabview codeinsert thissave

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


--
Don Guillett
SalesAid Software

"Tim" wrote in message
...
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

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
"Tim" wrote in message
...
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

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)

"Tim" wrote in message
...
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

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 wrote:

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)

"Tim" wrote in message
...
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

--

Dave Peterson

Do you resemble him Dave?<g

Bob

"Dave Peterson" wrote in message
...
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 wrote:

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)

"Tim" wrote in message
...
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

--

Dave Peterson

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


Bob Phillips wrote:

Do you resemble him Dave?<g

Bob