## Hot answers tagged conditional-formatting

13

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

8

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

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

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

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.

6

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

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

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

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

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

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

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

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

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

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

4

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

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

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

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

3

Sure. Apply the rule to all 40 rows and use a formula like this for each rule:
=$A2=1
That should only require five rules.

3

As someone who has done work in corporate IT before, I would expect them to just reimage the machine after you return it anyway. Is there any particular reason why you don't want to wipe it clean?

3

Edit: completely revised my answer. This one required a "helper column" but seems to work much better.
From David McRitchie's Excel Pages site on conditional formatting:
We want to Group anytime there is a
Change in Column A or Column B.
Conditional Formatting can not keep
track of what the previous color was,
so we will have to use a helper
...

3

You can use conditional formatting. Use the condition =NOW() + 90 with the Less Than... condition.
Choose whatever color format you want.
If you want to have multiple colors you can have multiple rules with different conditions where you vary the 90 from the above condition.

3

Try this formula instead: =COUNTIF($D3:$J3,"<0")>0

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

Select the entire range (using your example, B1:B10).
Add a new conditional formatting rule.
Select Use a formula....
the formula you use should be =(B1>=A1) (or =(B1<A1) for the red)
you will have to type this, as using arrows or clicking on the cells will insert a fixed reference with $ signs, which we have to avoid.
Use the Format button to ...

3

This uses cells in A1 to set the condition for cells in D1:D9 - alter ranges to suit your needs:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$A$1" and Sh.Name="Sheet1" Then
Sheets(1).Range("D1:D9").FormatConditions(1).Font.Color = Target.Font.Color
...

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