## Hot answers tagged conditional-formatting

41

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

25

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

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, ...

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.

9

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

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

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

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

7

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.

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

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" → "...

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

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

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

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, ...

6

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

Select the two columns. Go to Conditional Formatting and select Highlight Cell Rules. Select Duplicate values. When you get to the next step you can change it to unique values. I just did it and it worked for me.

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 your ...

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

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

5

Inserting and deleting rows does not cause conditional formatting to get fragmented.
The cause is copy/pasting between cells or rows using the standard copy/paste. The fix is to always use paste-value or paste-formula. On the destination right click and the Paste Options section will offer 123 (values) and f (formulas). Don't copy/paste formatting as that ...

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

4

Conditional format formula
=ISODD(SUM(IF(FREQUENCY(MATCH($B$2:$B2,$B$2:$B2,0),MATCH($B$2:$B2,$B$2:$B2,0))>0,1)))
Assumptions:
Range to apply formatting applies
from row 2 down
Apply above
conditional format to all cells in
row 2 that require shading
paint cell
format from row 2 to all rows that
require format
that the value to check for change is ...

4

You can try these:
VBA
Create a custom function with the following code:
Function IsFormula(ByVal Ref As Range) As Variant
If Ref.Cells.Count > 1 Then
IsFormula = CVErr(xlErrNA)
Else
IsFormula = Ref.HasFormula
End If
End Function
Example:
To check if any cells in column A have any formulas:
Highlight column A
Go to ...

4

Building on brettdj's answer, because I found the linked article quite difficult to follow:
Create a new Conditional Formatting rule and select Use a formula to determine which cells to format
Insert the following formula: =ISFORMULA(INDIRECT("rc",FALSE))
If you want the rule to apply to the whole worksheet, $1:$1048576 as the range to apply to. Otherwise, ...

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