Excel Conditional Formatting row background, skip cells with existing manual formatting

Question

I am looking to see if anyone has been able to apply conditional formatting to a row but skip cells that already have a background color.

I have a spreadsheet that pulls data from an SQL database using Get & Transform into tab 1.

That data is then merged with existing data from the Worksheet tab based on a common value. I get both sets of data in the same column order & then through a macro flag any where the new data has changed from the old (Worksheet) data by changing the background color to Purple. I also make the font bold.

This is done to visually flag that this cell value has changed since their last refresh of the data & stand out. This is done via conditional formatting where the new data <> old data ( D2 <> CQ2 then make bold purple). This is coppied to a 3rd tab and then a marco step then runs through and sets the values to the displayed values.

For Each aCell In mySel
         With aCell
           .Font.FontStyle = .DisplayFormat.Font.FontStyle
           .Interior.Color = .DisplayFormat.Interior.Color
           .Font.Strikethrough = .DisplayFormat.Font.Strikethrough
           .Interior.Pattern = .DisplayFormat.Interior.Pattern
         End With
     Next aCell

The existing worksheet data is then deleted & replaced by the new data. That all works just fine.

On the Worksheet tab there is a cell where the user can enter a number & through conditional formatting the entire row is changed to match the color set to that #. (IE 1 = Green, 2 = Yellow, 3 = Red, ...)

So what happens if the row already has one of these settings (it does get copied over during the refresh process) is the entire row is now Green, Red, Yellow, ./.. but what was formatted as Purple has now been replaced by the row level conditional formatting.

What we want to happen is to leave any individual cells formatted as purple and the rest of the row can be the selected color.

Simply setting a row level indicator or flag that something has changed would not be sufficient as the data spans columns A - BV.

For example here is code for if the color code choice is 1:

Selection.FormatConditions(1).StopIfTrue = False
    Range("A2:BV99999").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$BS2 = 1"
    Selection.FormatConditions       (Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.399914548173467
    End With

So basically I am looking to apply the above to all cells in the row range except for those that already have a background color set.

It would be preferable to do this in the conditional formatting statement so if there is already a purple cell with no row level formatting, if the users then sets this that purple cell stays purple while the others change to the selected color.

Is this even possible?

(guess I could maybe look cell by cell if it is bold & remove conditional formatting & re-format as Purple but that is slow for A3..BV1000 as an example)

May 17, 2018 I simplified things a bit. Where I need to apply what GMalc is suggesting is in the code for the row conditional formatting. I have tried:

Selection.FormatConditions(1).StopIfTrue = False
    Range("A2:BV99999").Select
   ' Range("BZ2").Activate
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$BS2 = 6"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    If Not Selection.Interior.Color = 153164535 Then 'have also tried vbLavender
    With Selection.FormatConditions(1).Interior
      '  .PatternColorIndex = 0
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249946592608417
      '  .PatternTintAndShade = 0
    End With
    End If

The entire row is still formatted as light grey. If I delete the 6 in column BS the Lavender background appears. I have tried changing the IF NOT to just IF and still formats entire row even if there is no coloring in the cell.

So this is really where I need to make the magic happen, color all cells in the row to the desired color unless it is Lavender then don't change that cells background color.

GMalc GMalc · Accepted Answer · 2018-05-16T18:48:49

Add an IF statement using Not aCell, you may have to change vbMagenta to the correct color.

For Each aCell In mySel
    If Not aCell.Interior.Color = vbMagenta Then 'add this line
         With aCell    
           .Font.FontStyle = .DisplayFormat.Font.FontStyle
           .Interior.Color = .DisplayFormat.Interior.Color
           .Font.Strikethrough = .DisplayFormat.Font.Strikethrough
           .Interior.Pattern = .DisplayFormat.Interior.Pattern
         End With
    End If  'add this line
Next aCell

Excel Conditional Formatting row background, skip cells with existing manual formatting

(guess I could maybe look cell by cell if it is bold & remove conditional formatting & re-format as Purple but that is slow for A3..BV1000 as an example)

1 Answers