remove the $ from your formula --- highlight B1:B3 --- COPY --- highlight C1:Z3 --- PASTE SPECIAL / click formats & OK


Z3 is just a cell go as far as required

hi Grizz. I am sorry I must be quite stupid here, but it doesn't work.

B2 = 320 C2 = 75
B3 = 80 C3 = 75
B4 = 36 C4 = 75

1st thing I do under new rule is click on C2 and do if cell value is less that B2 colour it red, when I check the rule is is

cell value <=A1
Format (font is red)
applies to = =$B$1

if I try and remove any of the $ from applies to, it puts them back when i hit apply

when I drag the handle down the applies to area showsd the range but it will only every use A1 as the cell to take information from, I need to to go to B1, C1 and so on.

when i do what you suggest below the cells in C are blank and also i dont need anything in Cell C, I need the number in Cell B to be red or green depending on if it is higher than the cell to its left.

Hi there! I understand that this is quite the old post. I was having the same issue with Excel 2010 but managed to resolve it. You need to delete the $ from the actual conditional formatting formula ("Use a formula to determine which cells to format") The $ is will reappear next to the "selected cells", even if you delete them but will stay deleted in the format where the formula is true.

-->You will highlight the cells you want to be formatted, select "New Rule..." and "Use a formula to determine which cells to format." Here you either want to hand type the formula or go back and delete the $ from the selected cells. Choose your formatting and then select "OK". The result under Conditional Formatting Rules Manager will show the Rule, Format, Applies to, and Stop If True. The formula under "Rule" needs to have NO $ in the formula. The "Applies to" selection will show $ before each figure but that seems to be okay.

I hope this helps!