Conditional Formatting on Change with Formula and Offset

October 1, 2022

Using Worksheet_Change(ByVal Target As Range), I would like conditional formatting to highlight the Barrel # of duplicate barrel entries based on the count in Column U. I have 12 other operations already in this worksheet, so am mindful of keeping it as quick and easy as possible for farm worker users. After varying degrees of failure, I think code below shows what I am trying to do. Thanks in advance for any help you can provide.

Dim myrange As Range
Set myrange = Range("U3:U10002")

If Not Intersect(Target, myrange) Is Nothing Then
    Target.FormatConditions.Delete
    Target.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="1"
    Target.Offset(-19).Interior.Color = RGB(255, 0, 0)
End If

>Solution :

Why do you want to use VBA? Excel can do Conditional Formatting without going through VBA. Add a new Conditional Formatting on the whole column B:B, "Use a formula to determine which cells to format", use this formula:

=IF($U1>1, TRUE, FALSE)

Then, add a specific format for cells on which the conditional formatting will be evaluated as "TRUE", like a red background color.

Note: don’t worry about the formula containing only a reference to the first cell in the column containing the count of each Barrel entry, when you apply a formula on a range, it is being "shifted" automatically by Excel for each cell in the range.