Conditional Formatting 5 values?

[mrkntdrk]

Can conditional formatting have more than three conditions? If so how?

I have five possible values coming from a very large vlookup table. I would like to have a separate color for each of the five.

The tables are already formatted correctly, so is it possible to bring the formatting over with vlookup?

Using:
=IF(ISNA(VLOOKUP(H27,AI1:AJ1000,2,0)),"",VLOOKUP(H27,AI1:AJ1000,2,0))

Thank you!!!

 

[Tom Urtis]

If the cells to be formatted are as you say (H2, K2, N2, Q2, and T2), then try this - -

First, right click on your sheet tab, left click on View Code, and delete any Change event code you have, because that will not apply in this case.

Next, paste the following procedure into that worksheet module. Press Alt+Q to return to the worksheet.

Post back and tell us if this is OK or you need further assistance. This worked fine when tested, based on your description of your spreadsheet. Modify for color index number.


Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Dim x As Integer
x = 8
Do
With Cells(2, x).Interior
Select Case Cells(2, x).Value
Case "Tom": .ColorIndex = 3
Case "Sue": .ColorIndex = 4
Case "Jan": .ColorIndex = 5
Case "Joe": .ColorIndex = 6
Case "Jim": .ColorIndex = 8
Case "John": .ColorIndex = 15
Case Else: .ColorIndex = 0
End Select
End With
x = x + 3
Loop While x < 21
Application.ScreenUpdating = True
End Sub

 

[mrkntdrk]

Tom,

It works great!!!

Thank you for your time!!! It is greatly appreciated.

Also, thanks to everyone else along the way.