Applying colors to specific cells

[encounter779]

I have 6 columns of numbers (search rankings) and I want to color cells in the second column that are less than the first in red and cells that are greater than in blue.

In the following columns I want to do the same thing except I want to compare the numbers in column 3 with column 2, 4 with 3, 5 with 4 and 6 with 5...

In addition it would be nice to bold or increase the font size on the cells that have a lesser or greater value of 10 or more in order to make them stand out.

Thanks!

[swatsp0p]

Select cell C1 (third col) and open Conditional Formatting from the Format menu:

set condition 1 to Cell Value is: greater than and enter "=B1" in the formula box then click the Format button. Set your font style on the (bold, color, etc.) on the Font tab, then move to the Patterns tab to set your background color. Click OK

next, click Add>> and repeat as above with the parameter "less than" and proceed to set font and cell colors.

(if you want, you can add one more parameter (3 is max)...maybe "is equal to"?)

Click OK to close the formatting window.

then simply copy this format (use the format painter or Copy/PasteSpecial> Formats) to the rest of your desired range.

HTH

[encounter779]

here is a sample of my excel file with search terms and rankings per date.


Searchterm1 2 4 3 6 3 5
searchterm2 7 8 4 6 2 7
searchterm3 9 1 3 2 1 1

I found the conditional formatting and it works great. The only problem I have is trying to copy the conditional formatting to all cells in the column. I tried the "paste special" option, but it copies the exact reference to a cell rather than a relative one. For example for "searchterm1" above, if I do my formatting for the second column (4), I compare it with column one (2). If I try to paste that formatting to the rest of the cells in column two it compares the cells in column two to column 1, row 1 rather than the corresponding number in column one in the same row. Does this make sense?

Where do I find the formatting painter that was mentioned?


Thanks for the help

[swatsp0p]

I'm not sure I understand why the formatting is not copying correctly. I understand that you always want to compare the active cell to the cell directly left in the same row. e.g. B1 compared to A1. As you then copy this formula around, it should maintain that "relative" reference. I would suggest you verify that you do not have an "absolute" marker ($) anywhere in your cell references, e.g. $B1 or B$1 or $B$1

The "Format Painter" I referred to is a toolbar button that looks like a paintbrush, usually located near the cut, copy, paste (clipboard) icons. Simply select the cell(s) you want to copy the format of, click the paintbrush and click/drag over the desired target cells. Only the formats will be copied, including the conditional format.

If your toolbar does not have the paintbrush, you can add it by right clicking on any toolbar, choose Customize, click the 'Commands' tab. Select 'Format' in the left pane and navigate to the 'Format Painter' icon in the right pane. Click and drag this icon to your desired location on your toolbar and release. Close the Customize box. Done!

HTH