Conditional formatting, per row reference to cell in row

Hey there
I have seen this forum and was very happy as I have an issue with excel 2010 that i couldn't solve.
I wrote a condition which works perfectly for one row and would like be able to copy it for the remaining 200 rows. But it doesn't work.

I want to do the following for each line
if the cell in row c contains "1", "2", or "3" then the color of rows G to H should change (to blue) that the users sees that he should not forget entries in those rows.
The rule description i have done in the conditional formatting rules manager is the following:
=OR($C24="1"; $C24="2";$C24="3")

The formula works fine (for row 24) but I would have to adapt it to every line therefore I tried just to copy it down. And there it gets strange to me: all lines meeting the condition get blue (what is good) but if I change after copying a cell in row C t(e.g. from "1" to "500") then the blue color remains and does not flip back to the original color (what I would like)

Is there another way which is copyable for the other lines in the conditional formatting? Or do I have to go for VBA? How would this then look like?

I would be very happy if somebody would accept the challenge to help me

Maybe look at this thread.
http://www.excelforum.com/excel-char...ster-list.html

How about posting a sample sheet to give us more of an idea of wat your wanting (make sure to remove sensitive informaiton)

Dear all I found out that in the sample sheet I prepared there was no problem with the copying of the formula but due to the complexity of the original sheet (over 300 Lines) somehow there was no auto-refresh of the conditional formatting.
For this I found the answer in the microsoft forum:


http://answers.microsoft.com/en-us/o...d-47cbfe9378d0

credit to the original poster:

-------------------------------------------------------------------------------------------------------
Solution for the auto refresh problem with conditional formatting in Excel 2007
Koen replied to Paul Strike
12-Feb-10 12:17 PM

1. Just set up your conditional formatting in the cell's you want.
2. Make sure you activate the "Developer" tab in the menu.
3. Do this by clicking on the Office button, click on Excel options and in the "Popular" section check the box ""Show developer tab in the Ribbon".
4. Click on the Developer menu and go to the Visual basic view.
5. Click on the menu View and select "properties Window". In that list you can find the "EnableFormatConditionsCalculation".
6. Set this value to "True" and the auto refresh will work.
7. Perform this action for all tabs in your sheet.
-------------------------------------------------------------------------------------------------------



Thank you anyway for your help