## Hot answers tagged conditional-formatting

5

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

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

3

In your conditional formatting window simply enter this formula:
=AND($B2<=TODAY(),$C2="No")
You may need to replace , with ; depending on your locality.
Note that conditional formatting works by checking a formula, and if the result is TRUE it applies the format. As a result you don't use IF statements, as these are used for applying a specified ...

3

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.

3

Assuming the groups are showing on Sheet1, the list of names and dates is on sheet 2:
select the table with the groups, including blank cells, starting in A1
click Conditional Formatting > New Rule > Use a formula to determine
enter the formula posted below, click the Format button and select the fill
Formula:
...

3

The formula does not have to be that complicated. Keep in mind that conditional formatting formulas can use relative references. When the format is applied to another cell, the references will be relative to the current cell.
Select cell M10 and enter the conditional format using this formula:
=OR(M10<J10,M10<K10,M10<L10)
Note that there are no ...

2

To format the cells if their date is before the current day (Sheet 1 = groups, Sheet 2 = dates and names):
Go to sheet 1, select all the data (A1:D7 in your example)
Click Conditional Formatting > New rule > Use a formula to determine which cells to format
Click the Format button and change the format to whatever you want (bearing in mind that this one is ...

2

To apply conditional formatting across multiple sheets within a workbook will take some work. There is no "point & click" solution that I have been able to find. Here are a couple solutions to help you find your way. The bottom line; you will have to do some experimenting to make this work for your specific application.
Using Defined Names - Excel ...

2

Sure. Select cell B2 and go to
Home tab > Styles group > Conditional Formatting > New Rule > Use formula to determine which cells to format
Enter this formula:
=AND(A2="John likes green apples",B2="John likes green apples")
Set the set the format to fill green.
Repeat for each additional color and rule.

2

The Microsoft Fuzzy Lookup Add-in does a great job for this kind of thing. Sadly, it won't work for Mac users, but does for PC users running Excel 2010 or later.
Download the add-in. (Make sure a Fuzzy Lookup tab appears on the ribbon.)
Copy all data in your first text column into a new tab/workbook. Insert a table for that column only (IE use the Insert ...

2

Once you select the cells that you want to format, you can find the Conditional Formatting drop-down on the ribbon, which gives you loads of pre-set rules to choose from, like color scales.
This will automatically color the items based on the range they are in…
You can also create your own rule to color only cells that fit within a certain range, or ...

2

Make sure that the referenced cell contains valid numbers.
To check this, put the formula =Mechanic!$C$8 in a blank cell. I.e., make sure C8 in worksheet named Mechanic contains 2 and C7 contains 4.
If they do contain the correct values then make sure they are formatted as number and not text. If these two are taken care of you should be good to go.

2

Select all cells on the sheet
Go to "Conditional Formatting" -> "New Rule..."
Under "Select a Rule Type:", select "Use a formula to determine which cells to format"
In the box below "Format values where this formula is true:", type in =$C$20>=$C$21
Then click on the "Format" button to choose which styling you wish to apply. For background colour, go to ...

2

No code required.
Select cells C15 to N15
click Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format
enter this formula
=$M15="yes"
select a format, for example green fill and close all dialogs
keep the cells selected and enter another rule with the formula
=$M15="no"
select a format, for example a red fill ...

2

All right, I got kind of sick of this question sticking out there. I wrote this macro, it sorts ascending, divides the non-blank range in half (rounded up) and applies two color scales. Modify as you wish.
Sub TestColorScale()
Application.ScreenUpdating = False
'sort ascending
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange ...

1

Are you willing to have the key values replicated onto the other sheets?
If so, set SheetY!Q3 to =SheetX!D3,
and then have SheetY!D3 conditionally formatted using the value in SheetY!Q3
(but otherwise the same conditional formatting rules as SheetX!D3).
You can hide Column Q when you get it working.

1

All conditional formatting formulas work as array formulas, so just enter the formula with enter.
Link to source

1

Select cell A1, then select column A (this is to ensure that A1 is the active cell within the selected range).
Open the conditional formatting dialog (the one from the New Rule option) and pick the last option; using a formula to determine the formatting.
In the entry box there, insert:
=B1="EUR"
Click on the Format button and format it as you would ...

1

I don't believe there is any method to dynamically choose the color when using Excel's built in Conditional Formatting. You could, as you guessed, use some VBA to accomplish though.
Assuming Column C has the lower date in the range range, Column D has the upper date in the range, Column E contains a colorindex (see here), and Column F has the date we are ...

1

I managed to find one solution, I recorded this macro:
Sub FomattingRules()
'
' FomattingRules Macro
'
' Keyboard Shortcut: Ctrl+e
'
Cells.Select
Cells.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)=0"
...

1

In Excel, this can be achieved with Conditional Formatting using a formula to determine which cells to format. You can achieve the result by selecting the relevant cells in columns B and C and applying the following rule:
=COUNTIF($B:$B,$B1)>1
This counts all the cells in column B that match the current row's cell in column B, and returns TRUE if the ...

1

I was thinking there must be a simple answer to this, then I tried it myself and yep, =A1=0 returns as 0 when blank!
I'm not sure why, but I found that swapping the statements in your first AND statement worked
=AND($B$2=0,$B$2<>"")
UPDATE
Following our comments: You're referencing an entire range in your formula, but conditional formatting checks ...

1

You can use a COUNTIF as alternative:
=COUNTIF(A1,0)
as the formula for conditional formatting. Blanks won't be highlighted.

1

The ISODD method kept on crashing my excel (30,000 records), so I did it another way. I ran a counter next to the variable I wanted to cond format on (so in the OP example, A=1, B=2 (each time col2 is different from one row above, add one). Then I used added a column with the formula =MOD(col2,2). Which gives an alternating 1,0,1,0 (all As 1, all Bs 0, all ...

1

If you are using merge fields, you can do something like this:
{ IF { =Sum(above) } <> 100 { = Sum(above) \*Charformat } { = Sum(above) \*Charformat } }
And format each field {} with the color you want.

1

Select the range you want to apply the conditional formatting rule to and go to Conditional Formatting on the Home ribbon, then New Rule.... From there choose Use a formula to determine which cells to format and enter the formula below.
=AND(COUNTA($A1:A1)=1,A1="")
Here A1 is the top-left cell of the selected range. Adjust the formula to fit your needs.
...

1

Excel will not allow a bare reference to another sheet in a conditional formatting formula.
To get around this, you can create a Named Range to represent the data on the other sheet. Conditional formatting allows references to named ranges, even if they are found on a different sheet.
Create Named Range:
Use Named Range in Conditional Formatting ...

1

If my understanding of your task is correct, it cannot be done using the 3-color scale with formulas only. You need to use relative references, which are not acceptable in formulas of the 2/3-color scale and icons set.
There are two possible solutions:
The one you used.
If the 1st tick in each rows should always be red, 2nd orange and so on, then you can ...

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