Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Changing a cell based on a different cells text

0

Hey,

I have created cell A1 to changed to red when I type 'No', and to also change to green when I type 'Yes'.

I now want a different cell to change colour based on if I either type 'No' in cell A1 (or when it turns red) which ever is easier/possible.

For example, I type No into A1, it then turns red, then cell D5 turns Blue.

The spreadsheet I have created has cells B2 through to B20 with conditional formatting to change to red and green based on yes and no. I now want different cells to change to a colour based on if any of those read no, or red, which ever is easier. 

The colour I want to change it to is: Hex: 8db4e2

Hopefully this makes sense.

Help would be greatly appreciated!

Thank you.

excelformattingformulas-functions
posted by: NeedinghelpPlease (rep: 2) on Wed Feb 8, 2023 at 7:28 pm
Answer
Discuss

Discussion

Hi and welcome to the Forum

Your question wasn't quite clear to me so I've given an Answer (below) and shown how to get the other cell (say F5) to go pale blue if any cell in B2:B20 contains "No" and by row (where F2 relates to B2, F3 to B3 etc.)

Next time you ask a question, please use the Add Files button to attach a representative Excel file (no personal data). It nearly always saves us time and we should be able to reply properly.
John_Ru (rep: 6142) Feb 9, '23 at 5:46 am
Add to Discussion

Answers

0
Selected Answer

Hi and welcome to the Forum.

You didn't attach a file to your question (which often saves us a lot of time!) but in the attached file, I've applied the various conditional formatting you seem to want. The CF cells have a pale yellow fill (which changes if the conditions are met.

Cell A1 (has data validation) and a green CF fill using the formula:

=IF($A$1="Yes", 1, 0)

so if you change if to Yes, it gets a green fill based on the CF formula:

=IF($A$1="No", 1, 0)

.

The latter CF formula  is used in cell D5 too but the fill is pale blue (Hex #8DB4E2) as you said.

Cell F5 will go pale blue if one or more cells in B2:B20 is/are "No" since the IF test is based on a COUNTIF:

=IF(COUNTIF($B$2:$B$20,"No")>0, 1,0)

Finally, in case you need it, cells H2:H20 will go pale blue if the corresponding cell in B is "No", based on the CF formula added to H2:

=IF($B2="No", 1,0)

and (carefully) changing the Applies to from =$H$2 to  =$H$2:$H$20 .Note that the single$ sign in the CF formula fixes only the column  

Hope this fixes your problem. If so, please don't forget to mark this answer as Selected.

posted by: John_Ru (rep: 6142) on Thu Feb 9, 2023 at 7:39 am
Discuss

Discussion

Thanks for selecting my Answer. 
John_Ru (rep: 6142) Feb 12, '23 at 2:35 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login