Conditional Formatting Formula Help

[larinda4]

Good morning,

I'm having an issue with setting the formula for my conditional format.

Some background information: I receive a report and copy and paste as values to the right hand side of it. Then I update all the figures with formulas to pull in correct numbers from various tabs. I want to compare the numbers to my original and highlight any cells that do not match yellow.

Here is my code:

'Define Range
Dim CombinedRange As Range
Dim FirstRange As Range
Dim SecondRange As Range

Set CombinedRange = Range("U" & FirstRow3 & ":X" & LastRow3 & " ")
Set FirstRange = Range("U" & FirstRow & ":X" & LastRow & " ")
Set SecondRange = Range("U" & FirstRow2 & ":X" & LastRow2 & " ")

'Delete Existing Conditional Formatting from Range
CombinedRange.FormatConditions.Delete
FirstRange.FormatConditions.Delete
SecondRange.FormatConditions.Delete

'Apply Conditional Formatting
CombinedRange.FormatConditions.Add Type:=xlExpression, Formula1:="=ROUND(U8,2)<>ROUND(G8,2)"
FirstRange.FormatConditions.Add Type:=xlExpression, Formula1:="=ROUND(U48,2)<>ROUND(G48,2)"
SecondRange.FormatConditions.Add Type:=xlExpression, Formula1:="=ROUND(U88,2)<>ROUND(G88,2)"

'Set interior color to red
CombinedRange.FormatConditions(1).Interior.Color = RGB(255, 255, 102)
FirstRange.FormatConditions(1).Interior.Color = RGB(255, 255, 102)
SecondRange.FormatConditions(1).Interior.Color = RGB(255, 255, 102)

My issue is here:
CombinedRange.FormatConditions.Add Type:=xlExpression, Formula1:="=ROUND(U8,2)<>ROUND(G8,2)"
FirstRange.FormatConditions.Add Type:=xlExpression, Formula1:="=ROUND(U48,2)<>ROUND(G48,2)"
SecondRange.FormatConditions.Add Type:=xlExpression, Formula1:="=ROUND(U88,2)<>ROUND(G88,2)"

The reports can be monthly, quarterly or annually so those cells change every time. How can I make the formula dynamic? Any help is appreciated!

 

[DanteAmor]

How can I make the formula dynamic?

Try the following:

The format starts at cell U8 and ends at the last cell with data in columns G through X.
The formatting is applied to column U:

Sub Conditional_Formatting()
  Range("U:U").FormatConditions.Delete
  With Range("U8:U" & Range("G:X").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row)
    .FormatConditions.Add Type:=xlExpression, Formula1:="=ROUND($U8,2)<>ROUND($G8,2)"
    .FormatConditions(1).Interior.Color = RGB(255, 255, 102)
    .FormatConditions(1).ModifyAppliesToRange .Cells
  End With
End Sub

But if you want it to apply up to column X, then change to this:

Sub Conditional_Formatting()
  Range("U:X").FormatConditions.Delete
  With Range("U8:U" & Range("G:X").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row)
    .FormatConditions.Add Type:=xlExpression, Formula1:="=REDONDEAR($U8,2)<>REDONDEAR($G8,2)"
    .FormatConditions(1).Interior.Color = RGB(255, 255, 102)
    .FormatConditions(1).ModifyAppliesToRange .Cells.Resize(.Rows.Count, 4)
  End With
End Sub