Conditional formatting

**pdmkh** · 11-15-2010, 03:55 PM

I need help appling multiple conditional formats based on cell text. The attached test sheet shows the formatting in place now but I need to be able to add more based on text selected from the drop down in column H, Text enterd in column P, and date ranges listed in column I,M,and N. Thanks in advance.

**Palmetto** · 11-15-2010, 04:00 PM

Versions of Excel earlier than 2007 are limited to three conditional formats.
You would need to use VBA to get around this limitation.

Get Around Excels 3 Criteria Limit in Conditional Formatting

**pdmkh** · 11-15-2010, 05:22 PM

Tried some examples from the link provided with no luck

Sub TheSelectCase1()

Select Case Range("H8").Text

Case "COMPLETE"

Range("A8").Value = "DONE"

End Select

End Sub

I need cell color of A8 to change when H8 equals complete.

**Palmetto** · 11-15-2010, 05:57 PM

Well, the syntax of the VBA code on that page is pretty clear and what you have posted is no where close to it.
Here is code example specific to your workbook, though all Case Statements have not been developed.
This code must go into the worksheet module accessed via right-clicking the sheet tab and choosing View Code. Copy this code and paste it into the code window of the VB Editor.
You should be able to see the pattern in this code easily. You did not specify what color Cells in column-A should be, so the value for iColor for each Case will need to the changed per your needs.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim iColor As Long
    
    If Target.Cells.Count > 1 Then Exit Sub
    
    If Not Intersect(Target, Columns("H:H")) Is Nothing Then
        If Target.Row > 7 Then
            Select Case Target.Value
                Case "COMPLETE": iColor = 5
                Case "READY": iColor = 7
            End Select
        End If
        Range("A" & Target.Row).Interior.ColorIndex = iColor
    End If

End Sub

**pdmkh** · 11-16-2010, 04:43 PM

Some questions;
1. looks like I have to remove the conditional formatting already applied to all cells on the work sheet in order to get formatting in VBA to work, correct?
2. I am not sure how to get the entire row up to column Q to change color based on text selected in column H. On the attached example provided originally cell color fro A8 to Q8 changes when DELAYED is selected.
3. What is the priority in the VBA code, again , on the attached example, A to Q is shaded grey when MCPA appears in column P. However A to Q shading is orange when DELAYED appears in column H.
I appreciate your help and patience in solving this issue.