VBA Conditional Formatting Multiple Sheets

[Michael Chandrapal]

Good afternoon,

I am trying to have multiple conditional formats run on multiple sheets. I tried recording the macro first and incorporating the code. I used "For Each sht In ActiveWorkbook.Sheets"
to designate the workbook and sheets. I am getting an error at the sht.range however. I think it's because I don't define it anywhere.

Secondly, I was wondering how I would add in another conditional format for the the following formula: = AND($BM1="CANADA",$AN1="1"). It's comparing 2 columns BM and AN to see if there is a match.

Please Login or Register  to view this content.

Any help understanding where I am going wrong would be helpful. I'm very new, for me to understand, sort of need it in its simpliest form.

Thank you,
Mike

[TMS]

If you really must apply conditional formatting to ALL those cells, try this:

Please Login or Register  to view this content.

I would recommend that you limit the range to those rows and columns where you actually expect to have data.

Regards

[Michael Chandrapal]

Awesome TM!

I ran it and it doesn't create too much of a lag and it isn't slow. It works perfectly!

Thanks for your help. I was wondering if you would be able to help in going about adding a second condition without doubling up the code... assuming I want it to be the fill/colour/shading is all the same.

Mike

[TMS]

Well, you're pretty much going to have to repeat the code between the outer With ... End With statements.

I think the easiest way to go about it, is to record a macro as you set the two (or more) conditions and then compare the recorded code to this code. You should be able to pick out the salient points.

If you struggle with that, record the macro and post it back. I'll massage it into the above format and structure.

Does this not make the workbook large? I'm not sure how Excel store its Conditional Formatting.

Regards

Regards

[Michael Chandrapal]

It's really weird, I am not able to record my steps when I use 2 more conditional formats (i.e. it only works with 1). I tried combining the 2 within the "With" statements. It didn't work, however. I tried stacking them as follows: This worked, but the conditional format for "FIXED INCOME" (the 2nd conditional format) started at 65532 for some reason.

Please Login or Register  to view this content.

Now, I'm more confused than before :s
Mike

[TMS]

I couldn't get it to record setting up the Conditional Formats at all.

However, where there's a will ...

There are two versions, Test2 and Test3.

Test2 is basically what you had with a second condition. I'm bemused that I don't have to change any numbers but, hey, I can live with that. Note that I have removed the quotes around "TRUE" as you don't need them ... and it didn't work.

Test3 is the same but I've dispensed with the INDIRECT. I think you get the same result without the complexity.

With regard to the range the format was applied to: I suspect that you need to have cell A1 selected when you run the macro.

Oh, and I didn't test on the full array of sheets.

Enjoy ...

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Michael Chandrapal]

Hey TM,

My apologies for taking so much time to write back. Great job on adding them together, I tried to run the formulas seperately, you combined them effortlessly.

I am still running into that problem where the conditional format does not start from the first row. E.g. the Conditional Format for $L1=Cash, after running the VBA with multiple conditional formatting, automatically becomes $L655530=Cash, not sure what's going on or why???

[TMS]

Wow, I've slept once or twice since then ... for a while, I couldn't remember contributing to this thread.

Try the code in a blank workbook ... change the array to include the basic Sheet1, Sheet2, Sheet3.

Please Login or Register  to view this content.

and check the effect.

It seems OK for me ... see the attached picture.

Regards