VBA Shade/Color Two rows at once in Excel 2007

[Dannottheman]

Hello,

I need to "shade/color" every two rows in Excel. I want to shade/color every two rows. For example: rows 4,5 (grey), 6,7 (white), 8,9 (grey), 10, 11 (white). In other words, I know I can shade "every other row" in Excel but I don't want to do this. I want to shade in pairs of two.

 

[Fluff]

You can do that with conditional formatting.
Select D2 down to the end of the data & in conditional, new rule, use a formula & use

=MOD(ROWS(D$2:D2)-1,4)<2

 

[Dannottheman]

When I click on "apply", nothing happens (please see image). I'm not sure what I'm doing wrong.

 

[Fluff]

You have not used the formula I suggested.
Also it looks as though you are using a Spanish version, in which case the formula would be

=RESIDUO(FILAS(D$2:D2)-1;4)<2

 

[Dannottheman]

Hmm, I thought I had used it. I went to Conditional Formatting, and selected the option "Use a formula..." then I copied pasted the formula and saved it. I highlighted the rows and then from Conditional Formatting, I selected "Administer Rules" (I'm translating from Spanish, not sure how it displays in English) and "Apply" and nothing happened. When I try to add this rule with the Spanish version you provided, I get a warning that there is an error in the formula.

 

[Fluff]

I suggested

        =MOD(ROWS(D$2:D2)-1,4)<2

but the formula in your image is

 =MOD(ROW()+1,4)<2

as you can see they are not the same.
Try my suggestion in a cell on the sheet & see if works, or gives an error.

 

[Dannottheman]

I see what you mean. Not sure how that happened. I tried it again (English version) and nothing happens, probably because I'm running a Spanish version. The Spanish version gives an error but I may just use the default Excel formatting so that the questions and answers are highlighted differently. Thanks anyhow for your help! Enjoy the Holidays and stay safe!

 

[Fluff]

If you want a macro, try

Sub dannottheman2()
   Dim i As Long
   For i = 2 To Range("D" & Rows.count).End(xlUp).Row Step 4
      Range("D" & i).Resize(2).Interior.Color = rgbLightGrey
   Next i
End Sub

 

[Dannottheman]

If you want a macro, try

Sub dannottheman2()
   Dim i As Long
   For i = 2 To Range("D" & Rows.count).End(xlUp).Row Step 4
      Range("D" & i).Resize(2).Interior.Color = rgbLightGrey
   Next i
End Sub

Works like a wicked charm! Thank you. Merry Christmas!

 

[Fluff]

You're welcome & thanks for the feedback.