VBA Shade/Color Two rows at once in Excel 2007

Dannottheman

Board Regular
Joined
Dec 24, 2020
Messages
55
Office Version
  1. 2007
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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,562
Office Version
  1. 365
Platform
  1. Windows
You can do that with conditional formatting.
Select D2 down to the end of the data & in conditional, new rule, use a formula & use
Excel Formula:
=MOD(ROWS(D$2:D2)-1,4)<2
 

Dannottheman

Board Regular
Joined
Dec 24, 2020
Messages
55
Office Version
  1. 2007
When I click on "apply", nothing happens (please see image). I'm not sure what I'm doing wrong.
 

Attachments

  • shade.jpg
    shade.jpg
    74.1 KB · Views: 5

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,562
Office Version
  1. 365
Platform
  1. Windows
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
Excel Formula:
=RESIDUO(FILAS(D$2:D2)-1;4)<2
 

Dannottheman

Board Regular
Joined
Dec 24, 2020
Messages
55
Office Version
  1. 2007

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,562
Office Version
  1. 365
Platform
  1. Windows
I suggested
Excel Formula:
        =MOD(ROWS(D$2:D2)-1,4)<2
but the formula in your image is
Rich (BB code):
 =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

Board Regular
Joined
Dec 24, 2020
Messages
55
Office Version
  1. 2007

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,562
Office Version
  1. 365
Platform
  1. Windows
If you want a macro, try
VBA Code:
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
 
Solution

Dannottheman

Board Regular
Joined
Dec 24, 2020
Messages
55
Office Version
  1. 2007
If you want a macro, try
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,562
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,141,707
Messages
5,707,983
Members
421,539
Latest member
zuniBM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top