If then excel

Chelle - Jan 20, 2022 at 11:22 PM
vcoolio
Posts
1345
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 20, 2022
- Jan 23, 2022 at 04:37 AM
Hello,

If AA cell says not recorded, I need AB, AC, and AD to be shaded in red.
I’ve read multiple forums and unable to find help for this.

Thanks

System Configuration: iPhone / Safari 15.1

1 reply

vcoolio
Posts
1345
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 20, 2022
249
Jan 23, 2022 at 04:37 AM
Hello Chelle,

You can use conditional formatting as follows:-

- Select cell AA1
- In the ribbon, go to Conditional Formatting in the "Styles" group and click on the little down arrow.
- In the drop down selection that appears, select "Manage Rules".
- In the Conditional Formatting Rules Manager dialogue box that appears, select "New Rule". The New Formatting Rule dialogue box will then appear.
- Select "Use a formula to determine which cells to format".
- In the long oblong box with the title "Format values where this formula is true:", enter the following formula:

=$AA1="Not recorded"

then click on Format and, in the "Format Cells" dialogue box that appears, select the Fill tab and select the red colour.
- Click OK.
- You'll be taken back to the "New Formatting Rule" dialogue box. Click OK again.
- You'll now be taken back to the "Conditional Formatting Rules Manager" dialogue box.
- Over in the "Applies to" box, click on the little up arrow.
- Change the =$AA$1 reference to =$AB$1:$AD$1 then click on the little down arrow. Click Apply and then OK in the "Conditional Formatting Rules Manager" dialogue box that re-appears. DON'T FORGET TO USE THE $ SYMBOL.

In your worksheet, type in "Not recorded" (without the quotation marks of course) in cell AA1 and you'll note that cells AB1:AD1 will be coloured in red fill.

If you have a greater range to conditionally format, change the "Applies to" range from
=$AB$1:$AD$1 to, for example, =$AB$1:$AD$20 then click on Apply and OK again.

I hope that this helps.

Cheerio,
vcoolio.
1