Conditional Formating

[Paul Brown]

Hi all,

I've put the attached code together from the web. Am trying to amend the
font colour in column A based on the value in column AI for rows 4 to 500.
The problem is that depending on the view some columns are hidden and so the
offset reference is a variable amount (I think it only counts visible
columns?) Can I name column A as the cell to be changed rather than offset
from column AI? (if so how!) The row will be variable based on the loop. I
can't use conditional formatting as am over the 3 allowed in Excel 2003. The
following code works fine when all columns are visible but not when some are
hidden. Many thanks.

Sub OrangeText()

Dim rng As Range

Dim rCell As Range

Set rng = Range("AI4:AI500")

For Each rCell In rng

If Val(rCell.Value) < 0 Then rCell.Offset(0, -34).Font.ColorIndex = 46

If Val(rCell.Value) >= 0 Then rCell.Offset(0, -34).Font.ColorIndex = 1

Next

End Sub

 

[Peter T]

In your routine, rCell always refers to a cell in Col-AI (35) and
rCell.Offset(0, -34)
will always refer to a cell in col-A on the same row irrespective as to
whether any columns are hidden.

Run the following with some/all/no columns hidden

Sub test()
Dim rng As Range
Dim rCell As Range

Set rng = Range("AI4:AI5")

For Each rCell In rng
Debug.Print rCell.Offset(0, -34).Address(0, 0)
Next
End Sub

Press Ctrl-g to view the debugged addresses in the Immediate window.

Regards,
Peter T


There might be something