Conditonal Formatting

Copper Contributor

Hi, I am trying to add conditional formatting to my worksheet.

 

In column, A there is the date

In column B, there is Money going in

In column C, there is money going out

in column D, there is where the money is going to/coming from.

 

So far I have added in this format to column D

'if column D contains the word 'wages', highlight green

'if column D contains the word 'rent', highlight yellow

 

I am looking to highlight column B & C as well as column D in these circumstances.

 

Can I add variables to these? for example

while highlighting column B - 'if column D contains 'wages' , highlight column B green

 

Thank you

3 Replies

Hi Aisha,

 

You may expand your rule for D if use in rule formula absolute reference for cell in D like $D2 and apply this rule to combined region like $D$2:$D$100,$B$2:$B$100

Thank you for your suggestion
I'm sorry, I do not understand the excel formulas. Am I able to copy and paste what you have suggested or do I need to alter it? If I need to alter it, is there a help document you could suggest? Thank you!

Hi Aisha,

 

How to use formulas with conditional formatting is here https://support.office.com/en-us/article/use-formulas-with-conditional-formatting-fed60dfa-1d3f-4e13... and in many other sources.

 

For such sample

image.png

we create two rules for two colors applying them to your columns from B to D (in ribbon Home->Conditional Formatting->New Rule->Use formula to determine which cells to format).

Formulas are

image.pngimage.png

just be careful with absolute and relative references.

 

And attached