CODEWITHSUNDEEP
vbaexcelconditional-formatting
Matthew Paulin
Matthew Paulin

Reputation: 59

vba condtional format loop failing

I have this code that almost works:

Sub condFormat()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    ws.Columns("B:B").FormatConditions.Delete
    ws.Columns("B:B").FormatConditions.Add Type:=xlExpression, Formula1:="=LEN(B1)>100"
    ws.Columns("B:B").FormatConditions(1).Interior.ColorIndex = 3
Next
End Sub

But it won't update the actual cell properly.

I end up with stuff like:

=LEN(B65517)>100

in column b cells.

How can I revise this to reference the correct cells in column B?

Any help appreciated.

Upvotes: 0

Views: 139

Answers (2)

Matthew Paulin
Matthew Paulin

Reputation: 59

I ended up just doing a loop since I couldn't get it to work the other way:

Sub condFormat()
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim myRange As Range, c As Range
For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    ws.Columns("B:B").FormatConditions.Delete
    Set myRange = Range("B2", Cells(Rows.Count, "B").End(xlUp))
    For Each c In myRange
    If Len(c) > 100 Then c.Interior.ColorIndex = 3
    Next
Next
Application.ScreenUpdating = True
End Sub

Upvotes: 0

Richard Morgan
Richard Morgan

Reputation: 7691

Cell references can get shifted based on the currently selected cell.

You can add an absolute reference to the B column:

ws.Columns("B:B").FormatConditions.Add Type:=xlExpression, Formula1:="=LEN($B1)>100"

But the easiest way is to probably explicitly select a cell, probably A1:

ws.Activate

ws.Cells(1, 1).Select

Here is a link from Microsoft explaining the issue.

Upvotes: 3

Related Questions