is it possible to write a macro or get the code to change the color of row depending on the cell value (in text, not numeric value).



To explain further - there are four options in the list box (or drop down). One option is - Started. When we select startred it should change the row color to yellow. when we select another option --- completed, then the row color should change to green.

You do not need a macro to do that, simply use conditional formatting.
Select the entire range of cells that you want to apply this format, then go to home tab-->conditional formatting-->New Rule-->Use a formula to determine which cells to format, then in formula field enter this( assuming that your range starts from A8, and in column A is your data validation list):
=$A8="Started" , and click on format button next to preview to pick a colour to fill the cells.
Create a second rule for the other colour, determine the cells to apply the rule with the formula :
=$A8="Completed"
And so on for other conditions...

Thanks, I have tried it. It works once but then it changes that particular cell only.

I have ensured that relevant cells in the rows are selected.

Here is a sample attached, work fine.

Attached Files

example.xls (23.0 KB, 16 views)

Hi Catalin,

Thanks, i have tried on your sheet and works perfectly, but no on the existing excel sheet i have. may be becasue it has macros and lot of list boxes. Any suggestions?

Without seeing a sample of your workbook, i can't give you an answer, sorry...

i cannot see the option to start a new thread? do you know whY?

You cannot start a new thread from your thread, only from https://www.msofficeforums.com/excel/
(for excel questions)