Help with Conditional Formatting in Excel 2

[Jock1970]

Hi all,

I need to format cells based on 7 different criteria so the normal 3 criteria option is no use.
I've read the earlier threads and copied the script given into VBE and it works, but not quite as I want it.
I have 2 problems with what I've done so far :-
1. The script colours the cell background, but I can't find where to see the list of colours so that I can get the cells to change to the colour I need. Are the colours and corresponding codes listed somewhere ?
2. The cells which are changed to fairly dark background colours need to have the font colour changed to white (I want 3 of my 7 options to have white text). What additions do I have to make to the script to get this to happen at the same time ?

The script I have in VBE at the moment is :-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Target
Select Case oCell.Value
Case Is = "P"
oCell.Interior.ColorIndex = 5
Case Is = "H"
oCell.Interior.ColorIndex = 3
Case Is = "F"
oCell.Interior.ColorIndex = 28
Case Is = "S"
oCell.Interior.ColorIndex = 36
Case Is = "T"
oCell.Interior.ColorIndex = 15
Case Is = "C"
oCell.Interior.ColorIndex = 48
Case Is = "O"
oCell.Interior.ColorIndex = 44
Case Else
oCell.Interior.ColorIndex = xlNone
End Select
Next oCell


End Sub

Thanks for looking. Hope you can help,

Regards,

Jock

 

[xlbo]

You can find out the colour indexes by running the following on a blank worksheet

for i = 1 to 56
 activesheet.cells(i,1).interior.colorindex = i
next i

To change the font colour, you need to use the
Range.FONT.colorindex
styntax - the colors are the same as for the interior.colorindex property

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[Jock1970]

Thanks Goeff,

I now have my background colours correct.

Excuse my ignorance, but I'm not sure what to do with the 'Range.FONT.colorindex' syntax - where do I put that ??

The script is now :-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Target
Select Case oCell.Value
Case Is = "P"
oCell.Interior.ColorIndex = 5
Case Is = "H"
oCell.Interior.ColorIndex = 3
Case Is = "F"
oCell.Interior.ColorIndex = 50
Case Is = "S"
oCell.Interior.ColorIndex = 6
Case Is = "T"
oCell.Interior.ColorIndex = 37
Case Is = "C"
oCell.Interior.ColorIndex = 26
Case Is = "O"
oCell.Interior.ColorIndex = 38
Case Else
oCell.Interior.ColorIndex = xlNone
End Select
Next oCell


End Sub

Perhaps you could add the syntax in one line as an example of how I should do it,

Thanks, in anticipation,

Jock

 

[xlbo]

For the ones you want to turn white, use:

oCell.Interior.ColorIndex = 38
oCell.FONT.colorindex = 2

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[Jock1970]

Cheers xlbo,

that works perfectly !!

 

[KenWright]

pssst Jock - notice that little sentence bottom left of each of Geoff's posts with a purple star against it

Just the way it usually works round here.

Best Wishes
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Jock1970]

Cheers Goeff (and thanks Ken for the reminder).

I thought I'd already gave you a star last week for your help on this thread but I'm obviously mistaken. Better late than never, eh !

Thanks again,

Jock