Help with conditional formatting for more than 3 option

[dannodidit]

Hi,

I need help creating a VBA script to change the fill color of part of a row based on selecting 1 of 5 options from a dropdown list.

Here how my worksheet goes. Column J contains a dropdown menu with 5 options (SAVE, LOSS, PENDING, NON RET, DUPL CALL). What i want to happen is when any of the 5 options a chosen, the fill color for that row from column B to K to change.

For example, cell J5 is changed to "SAVE" and from B5 to K5 the fill color becomes Yellow.

I've looked at other similar post but none of them deal with filling a part of row, only single cells.

I'm completely new to VBA scripts so i don't know where to start.

Thank you

Danny

[MSP77079]

I have shown a couple of ways to make the reference to the range and a couple of ways to define the color you want.

Please Login or Register  to view this content.

Note that although the RGB function appears to give you infinite control over the color hue and intensity, it doesn't really. Excel (at least through 2003) is limited to something like the 45 colors you see in the drop-down menu for coloring cells.

[dannodidit]

This is perfect. Thanks for all your help.

Danny

[dannodidit]

Hi MSP77079, i've run into a debug error. When i select more than 1 cell in column J and delete the contents, i get Runtime error 13 Type mismatch.

Example: I select cells J10 & J11 and hit the delete button and i get the error. It only occurs when more than one cell is selected.

Danny

[MSP77079]

Added error handling ...

Please Login or Register  to view this content.

[sphinx]

Hi

i used the follwoing code, i wonder if some can help me, since there is something wrong with updating the screen if the conditinal cell chaned based on formula

Please Login or Register  to view this content.

[dannodidit]

Debug error is fixed. Thanks again.

Danny

[MSP77079]

Hi Shinx,

Why do you have [Sheets("TRCK").Select] ???

For your second If [If Target.Column = 30 Then], there are only 2 options, right? So, you can use a "normal" Conditional format for this. See menu path Format >> Conditional Formatting.