Macro for fill color alternative rowsClosed

Question

Hello,

I want a macro for fill color alternative rows in excel selected table (only visible rows), anyone can you please help me.

venkat1926 · Accepted Answer

try this macro (this will color rows 2,4,6,8 etc red)

Sub test()
Dim j As Integer, k As Integer
j = Range("a1").End(xlDown).Row
'j is the last row
For k = 1 To j
If k Mod 2 = 0 Then
Cells(k, "a").EntireRow.Interior.ColorIndex = 3
End If
Next k
End Sub

if you want rows 1,3,5,7 etc to be colored 
change the following line in the above macro 
If k Mod 2 = 0 Then
into 
If k Mod 2 = 1 Then

jk · Answer

Hi,

But, I want to fill color in selected range (any range), which rows are visible (Not Hidden). if my rows are 1 to 10, hidden rows 3 and 6, then visible rows 1,2,4,5,7,8,9,10. now I want to alternate color these visible rows.

venkat1926 · Answer

contradiction between first message and laest one. first you want ALTERENATE ROWS to be colored. Now you want ALLTHE unhidden rows to be colored . please rephrase your question.

jk · Answer

I'm very sorry for that, I want ALL THE UNHIDDEN rows to be colored. Please help me. thx

venkat1926 · Answer

quote
I want ALL THE UNHIDDEN rows to be colored.
unquote
why do you want to color the unhidden rows. it is always in front of you anyhow try this mcro 

Sub test()
Dim j As Integer, k As Integer
j = Range("a1").End(xlDown).Row
'j is the last row
For k = 1 To j
If Rows(k).Hidden = False Then
Cells(k, "a").EntireRow.Interior.ColorIndex = 3
End If
Next k
End Sub

venkat1926 · Answer

try this


Sub test()
Dim j As Integer, k As Integer
j = Range("a1").End(xlDown).Row
'j is the last row
For k = 1 To j
If Rows(k).Hidden = False Then
'Cells(k, "a").EntireRow.Interior.ColorIndex = 3
Range(Cells(k, "A"), Cells(k, Columns.Count).End(xlToLeft)).Cells.Interior.ColorIndex = 3

End If
Next k
End Sub

nick316igr · Answer

Sub Test()
  Dim iNumOfRows As Integer, iStartFromRow As Integer, iCount As Integer
  iNumOfRows = Range("D61").End(xlDown).Row '--- counts Rows down starting from D61
    
    For iStartFromRow = 61 To iNumOfRows
    
        If Rows(iStartFromRow).Hidden = False Then '--- only unhidden rows matter
        
            iCount = iCount + 1

            If iCount - 2 * Int(iCount / 2) = 0 Then
                Rows(iStartFromRow).Interior.Color = RGB(220, 230, 241)
            Else
                Rows(iStartFromRow).Interior.Color = RGB(184, 204, 228)
            End If
    
        End If
    Next iStartFromRow
    
End Sub

Macro for fill color alternative rows

7 responses

Similar discussions

Subscribe To Our Newsletter!