#1
|
|||
|
|||
Dragging conditional formatting like a formula
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 |
#2
|
|||
|
|||
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
|
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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
|
#8
|
||||
|
||||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
Result!
Quote:
-->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! |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel Formula - Conditional replacement of cells | mag | Excel | 0 | 10-27-2012 08:30 PM |
Conditional Formula | UICOMP | Excel | 1 | 05-18-2012 12:59 PM |
Need formula for search and conditional formatting | MikeJedi | Excel | 9 | 01-13-2012 12:38 PM |
conditional formatting formula | NEHicks503 | Excel | 1 | 11-30-2011 09:01 AM |
Conditional formatting with AND, OR | Lucky | Excel | 2 | 10-03-2011 11:41 PM |