If then excel
Chelle
-
Jan 20, 2022 at 11:22 PM
vcoolio
vcoolio
- Posts
- 1345
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- May 20, 2022
Related:
- If then excel
- Transfer data from one excel worksheet to another automatically - Guide
- How to change date format in excel - Guide
- Excel @ in formula ✓ - Forum - Excel
- How to change author name in excel - Guide
- Compare two worksheets and paste differences to another sheet - excel vba free download ✓ - Forum - Excel
1 reply
vcoolio
Jan 23, 2022 at 04:37 AM
- Posts
- 1345
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- May 20, 2022
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.
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.
