Highlighting Customer (A5) when B5=1. Loop for B5 to B5000-CodePudding

This was working for a while in Excel 13.10 (32 bit), but needs to work in excel 2007, then suddenly it stopped working and gave a bug error 1004 on the line for R2.Interior.Color = RGB (255,153,255) and only after looping a few time. Here is my code:

Sub COL_Hilight()
   Dim i As Long, r1 As Range, r2 As Range

    For i = 5 To 5000
      Set r1 = Range("B" & i)
      Set r2 = Range("A" & i)
      If r1.Value = 1 Then r2.Interior.Color = RGB(255, 153, 255)
    Next i
End Sub

not sure why it worked for perhaps 5-7 times, then suddenly stopped. Please help.

CodePudding user response：

I can't understand why you don't use conditional formatting for this:

Manually it looks like this:

You can automate this as follows:

Range("B5:B5000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(A5=1)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = RGB(255, 153, 255)
    .TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

CodePudding user response：

The property Range.Interior.Color is not available in Excel 2007. It was first introduced in Excel 2010.

However, an alternative way is to use conditional formatting, which is available for your version (2007) of Excel.

Sub COL_Hilight()
    Dim ws as Worksheet
    Set ws = THisworkbook.Worksheets("Sheet1")
    
    
    With ws
        Range("A5:A5000").FormatConditions.Add Type:=xlExpression, Formula1:="=$B5=1"
        Range("A5:A5000").FormatConditions(1).Interior.Color= RGB(255, 153, 255)
        Range("A5:A5000").FormatConditions(1).StopIfTrue = False
    End with
End Sub

You may freely play around with this format to make it more dynamic, however the code above should add the conditional format according to your criteria through VBA.

Keep in mind that this assumes that your worksheet is named Sheet1.