Forum Discussion
Error message on already completed cells in excel
1. Select the range of cells in column B that you want to apply the conditional formatting to.
2. Click on "Conditional Formatting" in the "Home" tab of the ribbon.
3. Select "New Rule".
4. Select "Use a formula to determine which cells to format".
5. In the formula bar, enter the formula `=AND($A2="fruit",NOT(ISERROR(MATCH($B2,fruit_list,0)))))` if "fruit" is selected in cell A or `=AND($A2="vegetables",NOT(ISERROR(MATCH($B2,vegetables_list,0)))))` if "vegetables" is selected in cell A. Replace "fruit_list" and "vegetables_list" with the range of cells that contain the valid options for each category.
6. Click on "Format" and select the formatting that you want to apply to cells that meet the condition.
7. Click on "OK" to close the dialog boxes.
This should highlight any cells in column B that do not match any options associated with cell A.
Conditional formatting is a feature in Excel that allows you to apply formatting to cells based on certain conditions or rules.
Thank you very much for the reply! I just realise that I didn't express myself quite clearly in the original post. In this case the category is not static. Example, today the category can be named "fruit" tomorrow it can be anything else.
I'm creating a template where a user can decide what the category is. So, I need to insert a formula where it points to a specific cell, irrespective of its name.
To create the original dropdown list I created a dependent dropdown by using the "offset" & "match" capability to cater for the dynamic properties of the capabilities.
So, instead of saying "fruit" in the formula, how do I point excel to a dynamic range? I have 6 possible categories, with 6 possible related entries which can be anything as per the user's original entries.
Did you look at the sample workbook that I attached? It can easily be modified to work with a dynamic category list.
