I have a worksheet (Whole sheet) and I want to make a certain value show the
cell as a certain colour i.e. if the cell value is H then I want the cell to
be red, if a cell is L then I want it to be orange etc etc..... (I have
approx 8 differnt cell values and I want a different colour for each)

Can anyone tell me the correct syntax as I can't seem to get it right!!

Hi,

With a lot of options required I'd use code. Right click your shhet tab,
view code and paste this in. Note the syntax and add other cases as required.
If you don't know the VB colour number then record a macro of yourself
colouring cells to get the numbers.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
Select Case UCase(Target.Value)
Case Is = "H"
icolor = 3
Case Is = "F"
icolor = 8
Case Is = "G"
icolor = 10
Case Else
icolor = xlNone
End Select

Target.Interior.ColorIndex = icolor
End Sub


Mike

"NDT_Steve" wrote:

I have a worksheet (Whole sheet) and I want to make a certain value show the
cell as a certain colour i.e. if the cell value is H then I want the cell to
be red, if a cell is L then I want it to be orange etc etc..... (I have
approx 8 differnt cell values and I want a different colour for each)

Can anyone tell me the correct syntax as I can't seem to get it right!!

Thanks very much Mike
Works a treat!
--
Thanks and Regards
Steve


"Mike H" wrote:

Hi,

With a lot of options required I'd use code. Right click your shhet tab,
view code and paste this in. Note the syntax and add other cases as required.
If you don't know the VB colour number then record a macro of yourself
colouring cells to get the numbers.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
Select Case UCase(Target.Value)
Case Is = "H"
icolor = 3
Case Is = "F"
icolor = 8
Case Is = "G"
icolor = 10
Case Else
icolor = xlNone
End Select

Target.Interior.ColorIndex = icolor
End Sub


Mike

"NDT_Steve" wrote:

I have a worksheet (Whole sheet) and I want to make a certain value show the
cell as a certain colour i.e. if the cell value is H then I want the cell to
be red, if a cell is L then I want it to be orange etc etc..... (I have
approx 8 differnt cell values and I want a different colour for each)

Can anyone tell me the correct syntax as I can't seem to get it right!!

Glad I could help and thanks for the feedback

"NDT_Steve" wrote:

Thanks very much Mike
Works a treat!
--
Thanks and Regards
Steve


"Mike H" wrote:

Hi,

With a lot of options required I'd use code. Right click your shhet tab,
view code and paste this in. Note the syntax and add other cases as required.
If you don't know the VB colour number then record a macro of yourself
colouring cells to get the numbers.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
Select Case UCase(Target.Value)
Case Is = "H"
icolor = 3
Case Is = "F"
icolor = 8
Case Is = "G"
icolor = 10
Case Else
icolor = xlNone
End Select

Target.Interior.ColorIndex = icolor
End Sub


Mike

"NDT_Steve" wrote:

I have a worksheet (Whole sheet) and I want to make a certain value show the
cell as a certain colour i.e. if the cell value is H then I want the cell to
be red, if a cell is L then I want it to be orange etc etc..... (I have
approx 8 differnt cell values and I want a different colour for each)

Can anyone tell me the correct syntax as I can't seem to get it right!!