PDA

View Full Version : Colour visible cells in a loop VBA



rogered
11-23-2014, 08:55 AM
Hi, I have attached my SS which explains probably things more clearly. I have a column G of numbers 1 to x. I then have another macro which hides certain rows. i then want to identify when there is a break in the numbers e.g. 4,5,6,7 15. (a break of more than 2) which should then colour a corresponding cell in column B. Thanks for your help




Private Sub CommandButton1_Click()
Dim intRows As Integer
Dim intCols As Integer
Range("G37").Select

'would like to use ActiveCell.SpecialCells(xlLastCell).Select but this often changes to a random cell instead of the last visible cell in column G

'determine how many rows and columns to work on
intRows = Selection.Row
intCols = Selection.Column

intColour = 2

For i = 2 To intRows
For j = 7 To intCols

'change colour of first cell on each row
If j = 2 Then Cells(i, 1).Interior.ColorIndex = intColour

If Not Cells(i, j).EntireRow.Hidden And Not Cells(i, j).EntireColumn.Hidden Then

Cells(i, j).Select

'see if value same or different, then colour cell accordingly:
If Cells(i, j).Value > Cells(i - 1, j).Value + 2 Then
intColour = (intColour + 1) Mod 55
'avoid background colour being same as text (black in this case)
If intColour = 1 Then intColour = 2
Cells(i, j - 5).Interior.ColorIndex = intColour ' no selection needed to do this
Else
Selection.Interior.ColorIndex = Cells(i, j).Interior.ColorIndex ' this still needs to be solved, not sure of your intent
End If

End If

Next j
Next i
End Sub

Bob Phillips
11-23-2014, 11:50 AM
Not sure what you are trying to do


Private Sub CommandButton1_Click()
Dim intRows As Integer
Dim intCols As Integer
Dim intcolour As Long
Dim i As Long, j As Long

'would like to use ActiveCell.SpecialCells(xlLastCell).Select but this often changes to a random cell instead of the last visible cell in column G

'determine how many rows and columns to work on
intRows = Cells(Rows.Count, "G").End(xlUp).Row
intCols = Cells(2, Columns.Count).End(xlToLeft).Column

intcolour = 2

For i = 2 To intRows

For j = 7 To intCols

'change colour of first cell on each row
If j = 2 Then Cells(i, 1).Interior.ColorIndex = intcolour

If Not Rows(i).Hidden And Not Columns(j).Hidden Then

'see if value same or different, then colour cell accordingly:
If Cells(i, j).Value > Cells(i - 1, j).Value + 2 Then

intcolour = (intcolour + 1) Mod 55
'avoid background colour being same as text (black in this case)
If intcolour = 1 Then intcolour = 2
Cells(i, j - 5).Interior.ColorIndex = intcolour
End If
End If
Next j
Next i
End Sub

mikerickson
11-23-2014, 06:54 PM
Cross posted
http://www.excelforum.com/excel-programming-vba-macros/1051382-colour-visible-cells-in-a-loop-vba.html
http://www.ozgrid.com/forum/showthread.php?t=192025

At OzGrid there is some confusion about whether you want the highlighting triggered based on whether the preceding row is hidden or if the highlight is based on the difference between sequential visible cells.