How to apply Conditioning Formatting for whole worksheet

[Fanny18]

May i know how to apply conditional formatting for all sheets in worksheet?

I would like to delete the rows under the whole section of "Sales Product A"/"B"/"C" if there is no data for that section. or is there any better way to do it?

For eg, i will apply conditional formatting for each section by inputting the formula (if(A14="","True","False"), then apply font color to white.

Is there any other better way to do that using vba or how to apply such conditional formatting for all sheets? Thanks.

 

[6StringJazzer]

You can't set up conditional formatting for all sheets at one time (selecting multiple sheets then applying CF won't work). You can set up CF on one sheet, then use the Format Painter to copy the formatting to all other sheets, one at at a time. If this just needs to be done one time, that would be just as fast as writing VBA to do it.

 

[Fanny18]

You can't set up conditional formatting for all sheets at one time (selecting multiple sheets then applying CF won't work). You can set up CF on one sheet, then use the Format Painter to copy the formatting to all other sheets, one at at a time. If this just needs to be done one time, that would be just as fast as writing VBA to do it.

Format painter can be used to copy the conditional formatting formula as well? As my cf used formula to set the condition.

 

[6StringJazzer]

Format Painter copies ALL formats, including conditional formatting. It will copy the formula the same way you can copy formulas on a worksheet. So the formula needs to be correct in terms of absolute and relative ranges.

 

[Fanny18]

Format Painter copies ALL formats, including conditional formatting. It will copy the formula the same way you can copy formulas on a worksheet. So the formula needs to be correct in terms of absolute and relative ranges.

great, i have tried with format printer and it does work.

I have faced some other issues. As my data is alot and i used VBA to clear all the blank rows in the between, then the formula set inside conditional formatting is become #REF. how could this be solved?

 

[Fanny18]

May i know how to apply conditional formatting for all sheets in worksheet?

I would like to delete the rows under the whole section of "Sales Product A"/"B"/"C" if there is no data for that section. or is there any better way to do it?

For eg, i will apply conditional formatting for each section by inputting the formula (if(A14="","True","False"), then apply font color to white.

Is there any other better way to do that using vba or how to apply such conditional formatting for all sheets? Thanks.

View attachment 59634

my earlier formula is (if(A14="","True","False"), then apply font color to white. if i delete the row 5 to 9, then all the formula set in CF become #REF and cannot be used anymore. Anyhow to set the formula once the row up is deleted, it will changed itself accordingly? Thanks.

 

[6StringJazzer]

Instead of this

=IF(A14="","True","False")

use this

=A14=""

CF formulas are expressions that evaluate to TRUE or FALSE. Also, it must be the Boolean value, not a text string.

What is the Applies To range for that CF Rule? How many rules do you have? You should just have one rule per worksheet. Your comment "all the formula set" suggests maybe you have too many rules.

 

[Fanny18]

Instead of this

=IF(A14="","True","False")

use this

=A14=""

CF formulas are expressions that evaluate to TRUE or FALSE. Also, it must be the Boolean value, not a text string.

What is the Applies To range for that CF Rule? How many rules do you have? You should just have one rule per worksheet. Your comment "all the formula set" suggests maybe you have too many rules.

Thank you very much. It does help me alot.