VBA Background color setting in every "N" range

[Villalobos]

Hello,

I would like to set that every fifth range of background be light grey.
Could somebody offer a VBA solution for that?
I have uploaded the sample file.

Thanks in advance!

 

[Marc L]

Hi,​

​

easy way directly in worksheets with a format condition …​

​

By VBA, the range address is needed, almost the number of last line​

or the start line and the number of times to repeat the background …​

​

And of course you could do it yourself by using the Macro Recorder !​

​

 

[shrivallabha]

Any reason for using VBA?

• Select the total range where you need to apply this formatting. e.g. B1:I100
• Choose Conditional formatting | New Rule | Use a formula to determine which cells to format.
• Now insert following formula:

=MOD(ROW(),5)=0

• Then apply formatting as you need choosing Fill option of formatting and press OK.
• You should get what you want!

 

[Villalobos]

Hello Shrivallabha,

Thank you for your attention.
Previously I have thought about this method but the part of range is part of a pivot table and when I refresh the pivot table (the values will be overwrite) the background color is change back to white again. This would be the reason.

 

[Ronald Wilson]

Shrivallabha's solution will still work. Just make sure you position the conditional formatting in the first cell of your Pivot Table and select to Apply Rule To: All cells showing "xyz" values. Even if you change the selection in your table, it should redo your formatting.

Hope this helps.
Ronnie

 

[Villalobos]

Hello Guys,

Please, check the file that I have uploaded.
I hope that it will help to understand my problem.

After I refresh the pivot table the background color in Value field is change back to white.
I just want to extend the rule for the following range: Pivot sheet, B4:H11.

 

[shrivallabha]

Indeed, it changes the range references. Pivot and non-pivot area seem to have this issue.

Right click on the Sheet "Pivot" and choose "View Code" and then paste the following code in the module.

The conditional formatting code is cleaned up macro recorder code and I am not sure if something will change if your version is not Excel 2010. Post back if you have any issues / errors.

The idea is to delete old conditional formats in this particular range and apply them afresh

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
'\\ Set this to suit
Set rng = Range("B3:H" & Range("B" & Rows.Count).End(xlUp).Row)
'\\ Recorded code in Excel 2010 which is just cleaned up
With rng
    
    With .FormatConditions
    .Delete
    .Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)=0"
    End With
    
    .FormatConditions(1).SetFirstPriority
    '\\ See if this portion works fine if your version is not 2010
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.14996795556505
    End With
    
    .FormatConditions(1).StopIfTrue = False
    
End With

End Sub

 

[Villalobos]

Hi,

Thank you for your answer.
My version is Excel 2013 and unfortunately the above mentioned code is not working for me.

 

[Ronald Wilson]

Villalobos,
I have tried Shrivallabha's solution in both excel 2010 and 2013 and it works great. Keep it simple. Make sure there are no other Conditional Formats. Then, in the first active cell of your Pivot Table (i.e. B6), add a Conditional Format and select to Apply Rule To: All cells showing "xyz" values. Even if you change the selection in your table, it should redo your formatting.
What exactly do you mean by "refresh the Pivot Table"? Are you selecting new data? The method you are using to do the "refresh" could be erasing the conditional formatting.
I'm attaching your spreadsheet with the code I used. See how it works.

Thanks,
Ronnie

 

[Ronald Wilson]

Sample file

 

[Villalobos]

Hi,

Thank you for your patient, my problem has been solved.

 

[Ronald Wilson]

Was it saved by one of the methods here or did you find a new solution? Please share.