Drop down list does not work same as type

[Swapnil.Kupwade]

Hello,
I am tring to write a code to change color of cell when cell values matches perticular value.
Attached is the test file.
When cell value of B1 becomes "1" color changes to red.
The code works well if I type in cell B1.
When I use drop down list (Data/Validation/List) it doesn't work.

PLease help me.

Regards,
SWapnil

[FDibbins]

That cell does not contain any DV.

1. highlight B1
2. on the home tab, styles, select CF
3. select new rule, select "format only cells that contain"
4. Cell value...equals 1 format fill red

This will work for DV or manyal entry

[jaslake]

Hi swapnil-kupwade

Use Conditional Formatting on that Cell.

[arunm1008]

Sorry wrong post

[arunm1008]

Hi Swapnil,In your code, remove Auto_open and On entry, Instead add the following code in Worksheet_Change for Sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
KeyCells = "A1:B2"
If Not Application.Intersect(Target, Range(KeyCells)) _
Is Nothing Then KeyCellsChanged
End Sub

[Swapnil.Kupwade]

Thanks for reply,
I think problem was not well described. I want code should run when cell value is selected using drop down list.( Reason being in original case there will be a secentance of 20 to 30 letters.)

You will understand if you do below two cases.
First type "a" in cell B1.

Case 1) Type "1" in cell B1. Result will be cell colour changes to Red.
Case 2) Select cell B1 vaule from drop down list as "1". Result will be no change in color.

Use attached workbook.


Regards,
SWapnil

[arunm1008]

Hi Swapnil, I believe I had understood just the way you have redescribed it now. It is to trigger the code when a different value is selected that I was suggesting to move this event to the Worksheet_change event. I have also tested it what I suggested. Pls try it out. OnEntry does not trigger when different value is selected from list in B1, but Worksheet_change will trigger.
Arun

[Swapnil.Kupwade]

Hi arunm1008,
If you have tried & it is working, please upload the excel sheet. I have tried but didn't understand exactly what to delete.

Sorry i couldn't reply earlier.

SWapnil

[arunm1008]

Hi Swapnil, here you go, am attaching your excel workbook modified with my changes-Arun