I have put together some code to a sheet which looks at the value for each active cell in column A and then colours the row accordingly.
Column A will be full of products and what I do is colour each product alternately blue and yellow.
So what you may have is:
A B C
Product Reference Priority
Win2000 w2k01 H
Win2000 w2k02 M
Win2000 w2k03 L
Win2003 w2k301 M
Win2003 w2k302 M
WinXP wxp01 H
WinXP wxp02 L
In this instance the code would colour all the Win2000 as blue, then the Win2003 as yellow and then the WinXP as blue and so on.
The code is:
The problem is, when you apply an autofilter the colouring obviously goes awry. In the above example, if I filter out Win2003 then I would just have a blue block of products when ideally the code would only work on the visible items. (so Win2000 would be blue and Winxp would be yellow)
Is it possible for my code to only look at the visible items?
many thanks
Column A will be full of products and what I do is colour each product alternately blue and yellow.
So what you may have is:
A B C
Product Reference Priority
Win2000 w2k01 H
Win2000 w2k02 M
Win2000 w2k03 L
Win2003 w2k301 M
Win2003 w2k302 M
WinXP wxp01 H
WinXP wxp02 L
In this instance the code would colour all the Win2000 as blue, then the Win2003 as yellow and then the WinXP as blue and so on.
The code is:
Code:
If Cells(rowcnt, 1).Value = Cells(rowcnt - 1, 1) Then
Clor = Clor
Else
If Clor = 1 Then
Clor = 0
Else
Clor = 1
End If
End If
If Clor = 1 Then
ActiveSheet.Range(Cells(rowcnt, 1), Cells(rowcnt, 8)).Interior.ColorIndex = 19
Else
ActiveSheet.Range(Cells(rowcnt, 1), Cells(rowcnt, 8)).Interior.ColorIndex = 20
End If
Next rowcnt
The problem is, when you apply an autofilter the colouring obviously goes awry. In the above example, if I filter out Win2003 then I would just have a blue block of products when ideally the code would only work on the visible items. (so Win2000 would be blue and Winxp would be yellow)
Is it possible for my code to only look at the visible items?
many thanks