Conditional Formatting Help Needed!

[dukefan]

I am trying to set up 2 conditional formats. When row E is highlighted, I would like a conditional format where directly under variance if it shows a value greater than $0.00 it highlight background as orange similar how it shows highlighted when you see PENDING OR APPROVED. So anytime a value is greater than 0 in that row directly under variance it highlight background as orange.

Next, when row F is highlighted, I would like a conditional format where directly under OUSTANDING VAR, if value is $0.00, it should highlight background as red and if greater than $0.00 it should hightlight in green.

Under DECISION, I have that set to a Data Validation using list condition.

I want the the conditional formats based on the whole column and not by each cycle as you see there are 3 cycles now.

Please share screenshots of the conditional formats you recommend and what the results will show overall when it runs.

By doing each cycle where it has separate conditional formats which is how it it set up now as I copy and paste a blank cycle undereath each one that is complete to repeat the cycle, it slows the spreadsheet down and takes a bit to up open to as it has to recalculate through every conditional format in the conditional format section.

Please note, I will have the same information across as many has 45 tabs so I will have the same conditional formatting across all 45 tabs. If you have any way to set that up, please advise of your recommendation.

If you have questions, please reply with any and I will do my best to answer them.

Thank you in advance for any help you may provide in solving this issue.

 

[coulombevin]

Hi dukefan,

Here's what I would do:

- Variance: Select the cells B2:B1048576 then go to Conditional formating > New rule >Custom rule (the last option) then choose the format orange and enter the following formula

=AND($B1="VARIANCE",$B2>0)

- Outstanding Var: Do the same as previous, but select cells C2:C1048576 and with both formula

=AND($C1="OUTSTANDING VAR",$C2=0)

=AND($C1="OUTSTANDING VAR",$C2>0)

- What you'll have:

Bests regards,

Vincent

 

[dukefan]

Thank you Vincent.

Can you share a screenshot of the conditioning format for each condition listed from your reply?

My question is once the same conditional formating is set up over 45 tabs will it slow down the opening of the spreadsheet.

Also, is there an easy way to set up the conditional formating across 45 tabs.

 

[coulombevin]

Hi dukefan,

Unfortunately, my excel is in french you might end up lost in that section:

Also, to apply this formating to all sheets, i would look toward VBA to loop sheets, then in each sheet apply what you've done by hand (select column C from 2 to ..., appply conditionnal formating based on formula, repeat for each formating).

You can register what you're doing manual as a VBA macro by clicking the bottom left square with a record button :

to have an idea of the code that you should wrap with a for loop.

Bests regards,

Vincent

 

[dukefan]

Vincent,

Here is what I did and it is not working for me.

 

[coulombevin]

Swich the selected list (current selection) to "this sheet", then replace $F3 with $F1 and $F4=0 to $F2>0

 

[dukefan]

Vincent,

Is there anyway you can I can send you the spreadsheet as I am not able to get it working?

Chris

 

[coulombevin]

You might drop it on google drive and share it with "Everyone with the link - allow modification" And I would provide the modification I had to do while applying modification on your sheet if it's not against the forum rules

 

[dukefan]

Link to spreadsheet through google drive.

Loading Google Sheets

I have added some extra tabs with same information on three so I can see how each was modifed with conditional formatting.

 

[coulombevin]

It's on reading only mode, can't modify the sheet I've sent an email request