## Hot answers tagged conditional-formatting

37

Select the list in column A
Right-Click and select Name a Range...
Enter "ColumnToSearch"
Click cell C1
Enter this formula: =MATCH(B1,ColumnToSearch,0)
Drag the formula down for all items in B
If the formula fails to find a match, it will be marked #N/A, otherwise it will be a number.
If you'd like it to be TRUE for match and FALSE for no match, use this ...

23

It's even easier than pnuts' solution. You don't need to select the cell that holds the value that should be relevant for conditional formatting. Just select all the cells that should get conditionally formatted, and use a formula-based rule. Now, if your formula uses a cell address with fixed column (e.g. '$D5'), OpenOffice will adapt it for every selected ...

13

Here's a quick-and-dirty method.
Highlight Column B and open Conditional Formatting.
Pick Use a formula to determine which cells to highlight.
Enter the following formula then set your preferred format.
=countif(A:A,B1)=0

11

You can use a custom number format on the cell range:
"";"";"";""
This format string is basically saying display an empty string if the cell contains a positive number, a negative number, 0 or text. Error values will still be shown.

10

I tend to use an IF() rule for this sort of thing. The trick is then to get the '$' signs right. If column C is the original data and BO is the calculated value, then select the BO column and add a conditional formatting formula rule of:
=IF($C2=$BO2,1,0)
Note there is no $ sign beside the row number, which causes it to apply to each row in turn.

10

It's unclear if this is a bug; but Excel seems to be making the entire cell clickable just because there's a HYPERLINK function in the formula in order for it to work. Right-clicking on the cell & selecting Remove Hyperlink does not seem to work either.
I can't seem to find a non-VBA solution to prevent Excel from creating those "phantom" hyperlinks, ...

9

This answer is copied straight from stackoverflow.com Alternating coloring groups of rows in Excel.
I use this formula to get the input for a conditional formatting:
=IF(B2=B1,E1,MOD(E1+1,2)) [content of cell E2]
Where column B contains the item that needs to be grouped and E is an auxiliary column. Every time that the upper cell (B1 on this case) is ...

9

Select your cells.
Home Tab -> Format Group -> Conditional Formatting -> New Style:
Repeat step 2 with this:

8

Here's a macro that creates a conditional format for each row in your selection. It does this by copying the format of the first row to EACH row in the selection (one by one, not altogether). Replace B1:P1 with the reference to the first row in your data table.
Sub NewCF()
Range("B1:P1").Copy
For Each r In Selection.Rows
r.PasteSpecial ...

7

Took me forever to figure this out but it's very simple. Assuming data begins in A2 and B2 (for headers) enter this formula in C2:
=MATCH(B2,$A$2:$A$287,0)
Then click and drag down.
A cell with #N/A means that the value directly next to it in column B does not show up anywhere in the entire column A.
Please note that you need to change $A$287 to match ...

7

You need to anchor the column references in the formula for the conditional format.
Assuming that your data range begins in A1, you would set a conditional format on the range A1:F1 with a criterion formula $A1<>$A2 and your chosen format.
Then, you would copy the formatting of the row down the range (using Paste Special Format).

6

With the aid of a UDF (user defined function) you can use Conditional Formatting:
Function IsFormula(r As Range) As Boolean
IsFormula = r.HasFormula
End Function
And then use IsFormula in the format condition

6

If your Applies to field is set to A5:ZZ5 (or $5:$5 to highlight the entire row), your formula should be
=$J5="T"
Note where the $ is placed.
In my example below, my formula is set to =$J1="T" and my Applies to field is set to $A$1:$Z$6.

6

You can do this with formula-based conditional formatting.
Select the cells you want to apply the formatting to.
On the Home tab, click on "Conditional formatting" → "Manage rules..."
Click "New Rule..."
Select "Use a formula to determine which cells to format"
In the formula field, enter the following:
=SEARCH("Due in", $C1) > 0
Instead of C1, use ...

6

Select column M. Check which cell is highlighted (let's assume
M1)
Conditional Formatting \ New Rule
Use a formula to determine which cells to format
Type in the following formula =J1="paid" (using the highlighted
cell's row number - remember M1)
Choose your formatting, then validate.
Repeat as needed for the other conditions and formats.
for your other ...

6

You can use conditional formatting to do this by using XLM and Range Names
I have a longer article on Using XLM with Range Names and Conditional Formatting to automatically format spreadsheets according to cell content
Define a Range Name IsFormula =GET.CELL(48,INDIRECT("rc",FALSE))
Apply a conditional formatting cells testing for the formula, ie ...

6

The screenshots below are from Excel 2010, but should be the same for 2007.
Select the cell and go to Conditional Formatting | Highlight Cells Rules | Text that Contains
UPDATE: To apply the conditional formatting for the entire
worksheet select all cells then apply the Conditional Formatting.
(Click image to enlarge)
Now Just select whatever ...

6

When I need a range that shouldn't change under any circumstances, including moving, inserting, and deleting cells, I used a named range and the INDIRECT function.
For example, if I want a range to always apply to cells A1:A50, I defined a named range through the Name Manager:
In the Name Manager, add a new range (click New), and in the Refers To: field, ...

5

Instead of subtotal using a sum on another column, you can use subtotal using counta to see if a (known-non-blank) cell is hidden or not. For example, if column A will normally be visible (unless the row is hidden)
= IF( SUBTOTAL(103,A2)=1, "VISIBLE", "HIDDEN (or blank)" )
You can put this formula in a column that may be hidden, and it will still work.
...

5

Copy the column you want to format to an empty worksheet.
Select the column, and then choose "Remove Duplicates" from the "Data Tools" panel on the "Data" tab of the ribbon.
To the right of your unique list of values or strings, make a unique list of numbers. For instance, if you have 6 categories to color, the second column could just be 1-6. This is ...

5

What you suggest should work in combination with the right "applies to" range. For example if your data is in A1:C7 (with headers in A1:C1) then :
select the range without headers, i.e. A2:C7
use that formula (with preceding =), i.e.
=$C1<>$C2
apply required format
That should format rows 2, 4 and 7 as expected
...but I note your comments about ...

5

If you're using Office 2010, under "File -> Print -> Page Setup -> Sheet (tab)",
the setting for "Print area" should be what you are looking for. In my example, only columns A:J will be printed; however, everything is shown when viewing on screen.
This is what the dialog looks like:
(Click image to enlarge)
This may also be accessible as
"Page Layout" → ...

5

That works pretty much exactly as you noted:
Highlight A1:E20
Apply CF formula that is CUSTOM, this is a slight variation to your formula:
=A$20=MAX($A$20:$E$20)
Format ... Yellow
And the result is the MAX column will highlight

4

It's to do with precedence of rules. Rules higher in the list take precedence over those lower down the list. See here:
When rules conflict: For example, one rule sets a cell font color to red and another rule sets a cell font color to green. Because the two rules are in conflict, only one can apply. The rule that is applied is the one that is higher in ...

4

You can do this with a Worksheet Change event in VBA. I started building a simple example for you, but I realized your customer will probably want to be able to roll back any highlighting after they've checked your changes. So, I decided to make a whole working model that does everything. Here are the steps you'll need to follow:
Press Alt+F11 to open the ...

4

Select the cells from A3 to D3 then click on the Conditional Formatting then on the New Rule...
after that choose the option Use a formula to determine which cells to format and then write the following as the rule =$D3="no contact" click on the Format select the Yellow color and click Ok, and Ok again.
After that you can click on Conditional ...

4

Select the C5 cell and go to Home, Conditional Formatting. Choose "Manage Rules" and create a New Rule.
The new rule type is "format only cells that contain". The Rule Description should be "Cell Value equal to =$A$5". Then set the formatting you'd like on the cell.

4

Assuming Row1 is labels, the rows are as wide as ColumnZ and you want the formatting to apply to 1000 rows try =$B2>$B1 and apply to =$A$2:$Z$1000 (or adjust to suit).
Edit (hopefully for clarification):

4

See my array formula answer to listing A not found in B here:
=IFERROR(INDEX($A$2:$A$1999,MATCH(0,IFERROR(MATCH($A$2:$A$1999,$B$2:$B$399,0),COUNTIF($C$1:$C1,$A$2:$A$1999)),0)),"")
Comparing two columns of names and returning missing names

Only top voted, non community-wiki answers of a minimum length are eligible