Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a worksheet with a series of rows A1 - H1 etc and a drop down list which lives in H1, I would really like to change the cell styles of all the cells between A1 - H1 based on the value in H1.

Can anyone point me in the right direction? I've had little success with conditional formatting so far.

share|improve this question
What version of Excel do you have? – JNK Aug 5 '10 at 23:27
2007. So far i've got to =INDIRECT("M"&ROW())=1 which works, but I can't get =INDIRECT("M"&ROW())=Status to work for some reason! – toomanyairmiles Aug 5 '10 at 23:39
up vote 2 down vote accepted

Nailed it. Select the range of cells you want to format and then choose conditional formatting > manage rules. Create a new rule and choose "use a formula to determine which cells to format"

Enter =INDIRECT("M"&ROW())="Old" where M is the column you want to detect the value in and "Old" is the value you want the change based on.

share|improve this answer
Good job! I was going to give up but I'll put this one in my archives. – JNK Aug 5 '10 at 23:47

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.