Hi

I am having an issue with conditional formatting and i was hoping someone could help, I've tried a few searches and tried a few things but it does work

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



I CAN create a conditional format that says if B2 is equal to or greater than the number in that cell then the format the text green, and if it is less than 320 to turn it red.

But the problem is when I use the handle on the bottom right to drag down the "rule" still references cell B2 and C2.

Now if i did a formula like =B2+C2 and i dragged it down the next formula would change to =B3+C3 and then =B4+C4 and so on, I want the conditional format to do the same as the numbers in column B are all different.

Hope ive explained that clearly.

thanks in advance

try this --- right click on your cell and go COPY then left click and drag down as far as you need then right click PASTE SPECIAL and click on FORMATS and OK

Hi

Select all of column B or a range of cells in column B, then go to new rule on the CF in the home tab.

Rule 1: =$B1>=320
Rule 2: =$B1<320

Hi

Thanks for the replies.

Unless I am reading these wrong this still doesnt work

In my example

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

I need the numbers in column C to be green or red depending if they are higher or lower than the number to the left. becuase the number to the left is always different (320, 80, 36) I need the CF to change to

=$B1>=320
=$B1<320
=$B2>=80
=B2<=80
=B3>=36
=B3<=36

but i dont want to have to write this CF every time

when I select B2 through to B4 I get an error that says

you cannot use a direct reference to a worksheet range in a conditional formatting formula.

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.

if you have the $ in it will refere to that cell -- you can not drag it down you have to copy & paste special -- I dont have 2007 here but I'm not sure why it wants to put the $ back in

Perhaps post a sample sheet showing what you want to achieve

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Hi sjp1966
In cells:
B C
1 320 75
2 768 75
3 36 75
Select cells C2 - C4 ensuring the active cell is C2:

Home Tab > CF > New Rule

RULE 1: =$B2>=320

Rule 2: =$B2<320

Adjust your ranges etc to suit your needs

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!