Hello,
i'm not so familiar with conditional formatting, so I would like to ask something:
I'm making a doc where Column A has number values, column B also numbers, of rank positions. Conditional formating of cell in column B would be: if number is greater than number in cell A, colour it Red, and if it is less than number in cell A, colour it green (rank position dropped down). Now, I managed that, but since the document has lots of rows, how do I apply the formating on the other cells in the column? If I make copy/paste to them, formatting doesn't apply on the whole column since it is not a function, but a cell format. And do I have to do the formating every day for every next column ?
The "secret" is to make the formula with "relative referencing", hence, if your formula looks like this:
=$B$2>$A$2
then remove the $ sign before the row number, so it will look like this:
=$B2>$A2
With this, you actually don't need to copy the conditional formatting. You can select an entire range before applying it, and insert the formula as if you selected only the first cell at the top. Excel will know to apply the correct formulas to all the other cells in the range (assuming the range is a column).
Or you can apply this formula to the first row, and copy it with the format painter.
Thank you!! I cannot believe how long I had to search this morning to find a short, concise answer to a question that I feel shouldn't need asking. I just don't understand the thought process behind not having conditional formatting copiable across a range (relative) easily available.
T
This does appear to work for normal cells. However, if the range is in a pivot table, which is refreshed the conditional format is removed from the pivot table range. This doesn't happen in Excel 2003 any ideas?
hoolleeyy cow!!!
i've been thinking for 3hrs how to this, I even attempted to use macros but it still fails...
can u please explain what is the purpose of that dollar sign? pls? y u removed it?
hi,
i am searching for the similar solution, could you please help me.
I have multiple cells where I have data as "Yes" and "No". I need to color only those cells depending on the other cells value.
Example:
A B C D E
1 Mary y
2 Amy y
3 Joe y
4 Mary y
5 Joe y
So, here I want to color the B,C,D columns where there is "Y" and A column has value MAry with Red color.
Please throw some light, I was stuck for long time.
thanks in advance.
It's straightforward:
click on conditional formatting > new rule > =$b1="y" (copy and past this) . Once formlula has been entered, it should fill with your chosen colour. If your happy and wish to apply to other cells, edit copy, highlight entire column, then paste special format.
This is great. However I would like rows highlighted which are within a range. I tried the formula =($G2>0), ($g2<21) but it isn't working. what's the right formula to use?
Select all cells (Ctrl+a) (foobared in Excel 2003 hit Ctrl+A twice)
menu: Format, Conditional Formatting, Formula is on drop down box
Condition 1: =$D1<=1.00 [interior Color 35 Light Green]
Condition 2: =$D1<=1.05 [interior Color 36 Light Yellow]
Condition 3: =$D1>1.05 [interior Color 38 Rose]
Since the conditions are checked in order it is frequently unnecessary to have more than one comparison in a condition. For example in Condition 2 above, we do not need to write the condition as
=AND($D1>1.00,=$D1<-1.05)
I need to copy the last 6 of a 17 number and letter Vin: number from col B and past into col F also I need to take the year of the vehicle from col C and paste it into col H and to be placed infront of numbers all ready there. please can some one help
To get the last 6 characters of your 17 character VIN number that is stored in column B, put this in column F: =RIGHT(B2,6)
I don't understand "...and letter Vin: number..." please clarify.
If you mean that you want the word "Vin: number" in front of the 6 characters you copied from column B, then type this into column F: ="Vin: number "&RIGHT(B2,6)
If you need a space between "Vin: number and the 6 characters, within the quotes, make sure you place a space after the letter `r' in the word number.
I think that if you want to overwrite the numbers that are already stored in column H, you would need to run VBA. This, if not done correctly might destroy data. You might consider concatenating the year in column C with the numbers that are stored in column H. To do that you can, in a new column, let's say column J, type this: =C2&H2
If the existing numbers in column H are the result of a formula, you can simply modify the formula by placing this in between the "=" sign and the rest of the existing formula: C2&
So if (just an example) the existing formula in column H is =LEFT(B1,3), you would change the formula to =C2& LEFT(B1,3)
Select the first cell which you want to do conditional formatting. Do the condirtional formatting. Then select the cell, go to 'Conditional Formatting' ribbon (below the menu bar), then Manage rule, then edit rule, make the cell free; i.e. delete the $ signs. Hit OK.
Then again selct the first cell which has the original conditional formatting, then click 'Format Painter' (on the menu Ribbon, below the menu bar, with paint brush sign) then pain brush all the other cells where you want to apply the same conditional formatting.
has helped 1682 users this month