Trying to change cells' background color when there is conditional formatting.

[OaklandJim]

Mr Excel

In a previous post I wondered what I could try because I get inconsistent coloring of a data row using conditional formatting (described in a previous post this PM). Beyond frustrating!

I moved ahead with an approach involving the Change event. I wrote a standard sub to highlight cells in a data row if the entry in a status cell (in the respective data row) indicates that the person in the data row is not confirmed. I wrote Sub below to set background (interior) color of cells in the data row to bright yellow. BUT, it seems that it does not work if there is conditional formatting in place. Does that sound right?

Code does the highligting as designed if conditional formatting is cleared. I can reinstate conditional formatting and the color is still there which seems incongruous. (BTW, conditional formatting is to highlight every other row using Mod and a gray pattern).

If that isn't the answer do I really have to get rid of conditional formatting, do the highlight, then reinstate the conditional formatting? Errggh!

Here is my code which works if conditional formatting is cleared.

Sub DoRowHighlight(prUserCell)

    Application.ScreenUpdating = False
    
    Dim rCellsDataRow As Range
    
    Dim iIndexCol As Long
    
    Dim iNotesCol As Long
    
    Dim iColumnsCount As Long
    
    Dim iDataRow As Long
    
    Dim bConfirmed As Boolean
    
'   Only do highlighting if user changed a cell in the Status column.
    If Not Application.Intersect(prUserCell, [Prospects].Range("dStatus")) Is Nothing _
     Then
        
        With [Prospects]
            iIndexCol = .Range("Header_Index").Column
            iNotesCol = .Range("Header_Notes").Column
        
            iDataRow = prUserCell.Row - .Range("Header_Index").Row
        End With
        
        iColumnsCount = iNotesCol - iIndexCol + 1

'reports MAYBE 3/29
Debug.Print "Status cell = " & UCase(prUserCell.Value)

        bConfirmed = UCase(prUserCell.Value) Like "*CON*"
        
        Set rCellsDataRow = [Prospects].Range("dIndexes").Cells(iDataRow).Resize(1, iColumnsCount)
        
'reports $B$17:$U$17
Debug.Print "rCellsDataRow = " & rCellsDataRow.Address

            If bConfirmed _
                 Then
                    
Debug.Print "Confirmed"
'               Format cells in the data row to bright yellow fill/background.
                With rCellsDataRow.Interior
                    .Pattern = xlNone
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
            
            Else
            
'test cell value = not confirmed
Debug.Print "NOT Confirmed"
            
'               Format cells in the data row to bright yellow fill/background.
                With rCellsDataRow.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .Color = 65535
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
            
            End If
        
    End If

End Sub

 

[OaklandJim]

I reversed the order of my rules for conditional formatting. The highlighting for an unconfirmed person is first condition now and the alternating row shading is the second condition. That works.