How to loop through sheets in worksheet and apply conditional formatting

[Ianmacros]

Hi again! First of all, thank you to everyone on this forum. My VBA skills have improved a ton since I joined.

I need to apply conditional formatting to the same range on 8-10 different worksheets with the workbook. Since the highlighting is based on comparing the value to a different cell for each worksheet, I am thinking I will have to code out each worksheets format conditions. You will see that all that really changes is the cell references under "define the rule or each conditional format" from $D$4 to $D$5 (there are 8-10 different cell references, one for each sheet). Is there a way to loop through the 8-10 worksheets, changing the format conditions to reflect the changing comparison value cell reference?

Code for Worksheet #1

Please Login or Register  to view this content.

Code for Worksheet #2

Please Login or Register  to view this content.

[TMS]

Assuming that it is selective sheets rather than all sheets, you could have two arrays, one for the sheets, or sheet names, and one for the relevant cell addresses.

Then use

Please Login or Register  to view this content.

[Ianmacros]

Thanks, Trevor! Since I am still relatively new to this...is this the code change you are suggesting?

Please Login or Register  to view this content.

[TMS]

Yes, but rngArray(i) is NOT in quotes. That's the principle anyway, but you might need to tinker with it.

[Ianmacros]

OK, I'll try it out tomorrow and see what I can do. Thank you for the idea!

[TMS]

You're welcome.

[TMS]

I think it should look something like:

Please Login or Register  to view this content.

But you might have to tinker with the last condition ... or have a third array.

[Ianmacros]

I am working on a similar but more simple conditional formatting (for another project) as a way to get the hang of coding this. But my code is only formatting the first sheet in the array. Any tips?

Please Login or Register  to view this content.

[Ianmacros]

Realized I am probably missing some declarations, so I added:

Please Login or Register  to view this content.

But this still did not fix things...

[TMS]

Ok, I think it needs to look like this:

Please Login or Register  to view this content.

However, I can't test any of this a) because I’m on an iPad and b) because I have nothing to test it on

[protonLeah]

Please Login or Register  to view this content.

Your array has three elements, therefore the line

For i = LBound(shArray) To UBound(shArray)

will loop three times on the active sheet. It does not select any other sheet.

[Ianmacros]

Thanks, Ben! That code works. I appreciate the help.

Trevor, thank you for your suggestions as well!

[TMS]

You're welcome.