Ask a question
Report

# Conditional formating - apply on more cells [Solved/Closed]

Biljana - Latest answer on Jan 25, 2013 02:30PM

Read more

Ask a question
Report
# Conditional formating - apply on more cells [Solved/Closed]

Biljana - Latest answer on Jan 25, 2013 02:30PM

Read more

- Conditional formating - apply on more cells
- Add cells to a fixed reference range where conditional formatting applies
- Excel conditional formatting cell range
- Conditional formatting - Apply on more cells » How-To - Excel
- Conditional formating - Apply on more cells » How-To - Excel
- Conditional formatting based on another cell; apply to rows (Solved) » Forum - Excel
- Format based on another cell? (Solved) » Forum - Office software
- VBA - Conditional formatting- Apply color to last column » How-To - Excel

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.

You can have a look at the video named "Highlight the entire row based upon a cell value (with a formula)" in the following address:

http://www.microsoft-office-excel.com/conditional-formatting.html

It gives an example of using a similar formula with such "relative referencing".

Good luck!

=$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.

You can have a look at the video named "Highlight the entire row based upon a cell value (with a formula)" in the following address:

http://www.microsoft-office-excel.com/conditional-formatting.html

It gives an example of using a similar formula with such "relative referencing".

Good luck!

Thanks! Just the thing I was looking for! All I did was change the 'and' to 'or' and got the results I needed.

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)

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

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

Additional though:

Re: Column H

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)

Re: Column H

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)

Hi,

The easiest way is:

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.

The easiest way is:

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.

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

Parm- Dec 24, 2012 01:01PMKabir- Dec 26, 2012 01:27PMImmi- Dec 27, 2012 08:40AMBarrett Key- Jan 16, 2013 02:40PMNKL- Jan 25, 2013 02:30PM