Conditional/dymanic colour foramatting in excel

0
votes

Please help for conditional/dymanic colour foramatting in excel

For a cell, eg in row 2, cell B2, a drop down list can let user to select a value/text.

Then, based on the content of cell B2, the user have to enter the content of cell B3, B5,B6......B10...B20 (but not cell B4). In order to make this, i think the possible way is to dim the cell colour of cell B4 to let user know no need to enter the cell B4.

However, depends on the selection in cell B2, some cells in B3 to B20 may be dimmed case by case.

I have make a "criteria" sheet for reference which cells to be dimmed.

How to make it? Many thanks in advance!!!

1
excel
sorry i don't know to write marco, so marco is not fit for me !! - richard
have you looked at the help for conditional formatting, e.g. support.office.com/en-nz/article/… - Rhumborl
it doesn't help much - richard
You know the office help teach us about the one criteria or simple case. - richard
For my case, there is a "criteria" sheet. - richard

1 Answers

1
votes

Simple example:

enter image description here

Select B3:B10 and add a new CF rule, using the "use a formula" option, and enter:

 =NOT(ISERROR(SEARCH("|"&ROW()&"|",VLOOKUP(B$2,$E$2:$F$4,2,FALSE))))

The lookup table has the rows which should be "greyed out"