Reputation: 1

How to apply conditional formatting on overlapping ranges (VBA)

I applied different conditional formattings on an excel sheet but when I update the data with a macro, the ranges change, the formatting rules multiply, so it becomes a hot mess. To avoid this, I've decided to write a code for conditional formatting that is going to be inserted at the end of the code that updates the data on the sheet.

The only issue is that if two ranges overlap, the formatting gets mixed up.

UPDATE! @artodoro suggested to try different ConditionalFormatting numbering which I tired before but for some reason when I did it it did not work, when I pasted his code it did. Anyways, I now have updated my code but I could not get rng6 to work, so I had to modify the range afterwards. Does anybody know why it did not work with ConditionalFormatting(3)?

The updated code:

Sub Re_Audit()

Dim wsSmy As Worksheet
Dim LastRow As Integer, DeleteRow As Integer, LastColumn As Integer
Dim StartCells As Range
Dim rng1 As Range, rng2 As Range, rng3 As Range, rng4 As Range, rng5 As Range
Set wsSmy = Workbooks("HRDA Audit_Master.xlsb").Sheets("SUMMARY")
Set StartCell = wsSmy.Range("A1")
LastRow = wsSmy.Cells(wsSmy.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = wsSmy.Cells(StartCell.Row, wsSmy.Columns.Count).End(xlToLeft).Column
wsSmy.Activate

Call DeleteConditionalFormatting

Set rng4 = wsSmy.Range("$N:$N")
With rng4
    .FormatConditions.Add Type:=xlExpression, _
                    Formula1:="=$Q1=""Age Under 18"""
    .FormatConditions(1).Interior.Color = 15773696
    .FormatConditions(1).StopIfTrue = False
End With

Set rng5 = wsSmy.Range("$E:$H")
With rng5
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=OR('Mismatch Finder'!$B1=""Org vs Work Country Mismatch; "",'Mismatch Finder'!$C1=""Org Name vs Work Location & Country Mismatch; "")"
.FormatConditions(1).Font.Bold = True
.FormatConditions(1).Font.Color = -16776961
.FormatConditions(1).StopIfTrue = False
End With

Set rng3 = wsSmy.Range("=$I:$J,$L:$L")
With rng3
    .FormatConditions.Add Type:=xlExpression, _
                    Formula1:="='Mismatch Finder'!$G1=""Salary Band, Pay Basis/Grade Mismatch; """
    .FormatConditions(1).Font.Bold = True
    .FormatConditions(1).Font.Color = -709715
    .FormatConditions(1).StopIfTrue = False
End With

Set rng1 = wsSmy.Range("$O:$P")
With rng1
    .FormatConditions.Add Type:=xlExpression, _
                    Formula1:="=$Q1=""Working Hours Discrepancy"""
    .FormatConditions(1).Interior.Color = 49407
    .FormatConditions(1).StopIfTrue = False
End With

Set rng2 = wsSmy.Range("$I:$K")
With rng2
    .FormatConditions.Add Type:=xlExpression, _
                    Formula1:="='Mismatch Finder'!$F1=""Pay Basis/Grade vs Annualization Mismatch; """
    .FormatConditions(2).Font.Bold = True
    .FormatConditions(2).Borders.LineStyle = xlDashDot
    .FormatConditions(2).Borders.Color = -16777024
    .FormatConditions(2).Borders.Weight = xlThin
    .FormatConditions(2).Borders.Weight = xlThin
    .FormatConditions(2).StopIfTrue = False
End With

Set rng6 = wsSmy.Range("B:D")
With rng6
    .FormatConditions.Add Type:=xlExpression, _
                    Formula1:="='Mismatch Finder'!$D1=""Org Name vs Pay Basis/Grade Mismatch; """
    .FormatConditions(1).Interior.ThemeColor = xlThemeColorAccent1
    .FormatConditions(1).Interior.TintAndShade = 0.599963377788629
    .FormatConditions(1).StopIfTrue = False
End With

rng6.FormatConditions(1).ModifyAppliesToRange Range("$H:$J")

End Sub

If I set .FormatConditions(3). for rng6 it does not apply the formatting properly, it applies it for rng2's formatting.

Set rng6 = wsSmy.Range("$H:$J") 'or ("H:J")
With rng6
    .FormatConditions.Add Type:=xlExpression, _
                    Formula1:="='Mismatch Finder'!$D1=""Org Name vs Pay Basis/Grade Mismatch; """
    .FormatConditions(3).Interior.ThemeColor = xlThemeColorAccent1
    .FormatConditions(3).Interior.TintAndShade = 0.599963377788629
    .FormatConditions(3).StopIfTrue = False
End With

Upvotes: 0

Answers (2)

artodoro

Reputation: 45

How Conditional Formatting Works

So, a Range object has a FormatConditions property. This is a collection of FormatCondition objects. It is this collection that contains all the information about conditional formatting for the range. When we select a particular range and add a new condition, we get the following:

This is possible because this object has an AppliesTo property that returns the range to which the formatting is applied. In the screenshot below you can see that if we select the first element FormatConditions(1) of UsedRange and FormatedRange, call their AppliesTo property and its Address property, we will get the same address:

Next, when we change the range of the FormatedRange object to Columns(2), add a new FormatCondition object through it and apply the new formatting, we can notice that the FormatedRange has changed the address of the first FormatCondition element, while the UsedRange has the same FormatCondition address. If we expand the FormatConditions collection, we will see that UsedRange has two elements, and FormatedRange has only one:

That is, it turns out that if one range overlaps with another range that has conditional formatting, it is present in that range's collection (UsedRange has two such elements in the example).

What's going on in your case

Note, when I executed the Add method, I assigned its return value to a variable and then worked with that variable. This is important.

In your case, you are creating a new FormatCondition object for the range, and if there is no more conditional formatting in this range, then referring to the first element of the collection you will get your newly created FormatCondition object. But if the range overlaps with a range where conditional formatting has already been created, your newly created object becomes element 2. This is the reason why I told you in my reply to replace 1 with 2, but the more correct way is to assign it to a variable when creating a new item.

So to make sure you are working with the new conditional object you just created - use a variable to get that object and apply the formatting in its properties as in my examples.

The code from the screenshots:

Public Sub Test()
  Dim FormatedRange As Range: Set FormatedRange = Sheet1.UsedRange.Columns(1)
  Dim BoldTextFormat As FormatCondition
  Set BoldTextFormat = FormatedRange.FormatConditions _
                                    .Add(xlExpression, Formula1:="='Sheet1'!$D$1=""Value2""")
  With BoldTextFormat
    .Font.Bold = True
  End With
  
  Set FormatedRange = Sheet1.UsedRange.Columns(2)
  Dim RedBackFormat As FormatCondition
  Set RedBackFormat = FormatedRange.FormatConditions _
                                    .Add(xlExpression, Formula1:="='Sheet1'!$D$1=""Value2""")
  With RedBackFormat
    .Interior.Color = vbRed
  End With
End Sub

Upvotes: 2