Conditional formatting based on list of numbers - select number 4000

Hi Guru's

I have list of numbers 1000,2000, 3000, 4000, 5000, 6000 and 7000 in A2 List box. I have made conditional formatting if cell is blank highlist with no color.
Now If it is 4000 ... It has to highlight with color certain cells of the row. We can fill the data in them.

Please advise formula. I appreciate your help and co-operation.

Regards
uday

Hi McStagger,

Thank you for the reply. I appreciate it. I am looking for, if I select A2 - 4000 then it should highlight with color for C2, F2, I2, J2 cells. So that other users fills the data in CE, F2,I2, J2 instead of filling data in wrongs cells.

Regards
Uday

To do that you would still use the above formulas in a CF, you will just need to specify the cells in which the CF is applied. If you post a sample workbook, it may be easier to assist; this is also the preferred method of working with questions set by the forum moderators so dont be surprised if one jumps in and mentions this directly.

I am sorry it is typo mistake not CF , it is C2 Cell. - Uday

CF = Conditional Formatting

Hi

I am attaching the excel sheet

Your Excel sheet isn't working as it is dependent upon another sheet that is not included.

That said, you mentioned that if 4000 is selected that cells C2, F2, I2, J2 will highlight. What happens if a different number is selected, will different cells highlight?

Hi McStagger,

I apologize for the delay in response, I am assigning this to user to fill the data which is required for 4000 only but not other cells. User knows that what fields he is going to fill it up.

Regards
Uday

Fair point, xlnitwit. The $A2 was an oversight, I tend to lock everything down on an initial setup just to make sure things are working.

As far as "AND" is concerned, it's a habit. I tend to use "SUM" in a similar way.

Hi

I am not able to understand, would you please let me know step by step.

Regards
Uday

To recreate the sheet I wrote, do the following:

1. In cell A2, enter your test value 4000
2. Under the 'Conditional Formatting' button, click 'New Rule' Note: Cell A2 should still be selected, but not necessary.
3. A new window titled 'New Formatting Rule' should have popped up. In this window click on 'Use a formula to determine...', it is at the bottom of the list.
4. In the formula box, enter your formula. In this case I used "=AND($A2=4000)"
5. Now click on the 'Format' button and select how you want to relevant cells to be displayed.
6. Click Ok, you should now be back to the main sheet but you will notice that nothing has happened except for A2 being highlighted.
7. Click on 'Conditional Formatting' -> 'Manage Rules'
8. A new box title 'Conditional Formatting Rules Manager' should have popped up. Next to 'Show formatting rules for:' is a drop down list, select 'This Worksheet'
9. You should now see your conditional formatting. In the column 'Applies to' enter the cells you want this rule applied to (C2, F2, I2, J2). Click Apply.
10. If something funky happened and not all the cells lit up as expected, it is likely because Excel adjusted the initial A2 assignment. Simply click on 'Edit Rule' and remind Excel that the lookup cell is $A2. Note: The "$" is important to include otherwise the formatting will not work.

Does that help?

Hi McStagger - It doesn't work. - Uday

Can you please upload your attempt so I can take a look at where things may have gone wrong?