Tuesday, December 4, 2018

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

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.

No comments:

Post a Comment

plot explanation - Why did Peaches&#39; mom hang on the tree? - Movies &amp; TV

In the middle of the movie Ice Age: Continental Drift Peaches' mom asked Peaches to go to sleep. Then, she hung on the tree. This parti...