## Hot answers tagged conditional-formatting

27

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

17

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

9

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

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

8

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

8

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.

7

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.

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

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

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

5

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

5

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.

5

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

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

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

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

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

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

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

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

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

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

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

3

Formula for cell A1:
=IF(LEN(A1)>5,TRUE,FALSE)
Note that your formula above does not have the $ symbol in it.
Then go to Conditional Formatting > Manage Rules > Applies to
and change the value =$A$1 to =$A:$A
PS - note that it isn't necessary to encapsulate the length inside of an if statement. It's just part of my personal preference as I find ...

3

You can only apply one data bar rule to a set of cells. You can, however combine data bars and a conditional cell fill based on a formula like
=A1<=150
This will highlight the whole cell, though.

3

I don't believe there is a way to make a cell cycle through formats using Excel's native conditional formatting functionality (you can probably do it in VBA, but that seems overkill to me). However, there's a simple enough workaround:
For each rule, you can use different formats that aren't incompatible with one another. For instance, Rule 1 can apply a ...

3

You can also use the following as an 'easier to read' option.
If you wrap the string that you want to search for in quotation marks, it will work fine.
So select 'Cell Value is', 'equal to' and in the criteria box put "Yes", including the quotation marks. This should now work.

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