## Hot answers tagged conditional-formatting

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

Actually, yes, it can be done through conditional formatting... Sort of. Use the screenshots below to setup a conditional format that uses a custom number format for cells that are TRUE. You should be able to extrapolate the setup for False = No.
It's important to note that it will only change the apparent value in the cell and not the actual value. If you ...

2

Rather than using Specific Text, try using Cell Value and equal to, then enter C for one rule and O for the other (do not enter equal or quotes when entering the letter, Excel does it for you). This will look for the only the letters that match.
Applied to the whole column, this ignores text of the Open / Closed heading.

2

Try this out. Highlight cell C3:D4 (as per example) and add conditional formatting with the following formula:
=AND(ROW(C3)-2=DAY($A$1),COLUMN(C3)-2=MONTH($A$1))
I have tested this and it works as described so let me know if you have any problems.
Note: this formula excludes year as a factor, so 1/1/2000 would highlight cell C3, as would 1/1/2015
Copy ...

2

Use relative formulas ($ or not) and set the Applies to range to the entire row.
Just make sure that the first row in your formula and the first row in your Applies to are the same.
(Screenshots show just some range example. Change to match your exact setup.)

2

There may be neater solutions, but you shouldn't need to concatenate first and last names to make a "FullName" column.
Preview of our output
(the white cells on the right show what output our formula will produce to drive the conditional formatting):
Here's our formula
=COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2)
...

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

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.

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

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

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 I understand what you are asking, you can accomplish this with two conditional format rules which use formulas. You didn't state what version you are using, so you will have to use whatever method you need.
The first rule will use the following formula: =AND(NOT(ISBLANK($L1)), $A1="Flag set") This checks to see that L is NOT blank AND A1="Flag set". If ...

1

Select cell A1 (if you don't, these directions will not work due to the relative reference you create in step #3...which is the key to being able to apply these conditions to all the entire worksheet)
Create a new Conditional Formatting rule of the Use a formula to determine which cells to format type
In the Format values where this formula is true: text ...

1

You can use a conditional format to highlight every other set of unique values.
=MOD(SUM(--(OFFSET($A$1,0,0,MAX(ROW(A1)-1,0))<>OFFSET($A$2,0,0,MAX(ROW(A1)-1,0)))),2)
You can significantly shorten the formula if you have a maximum range you want to apply the formatting over, but it would be less robust.
=MOD(SUM(--$A$5:$A5<>$A$6:$A6),2)

1

Use the following formula in the conditional formatting. I assumed row 1 is the header row.
=MOD(ROW(),10)-1=1

1

Select the cells in column A. Create a new conditional format based on a formula (Click Conditional Formattting > New Rule > Use a formula to determine which cells to format).
Use this formula
=COUNTIF($B:$B,A1)
Click the Format button and select a format for example a fill color. Click OK.

1

The way I always make Gantt chars in Excel is to put a formula in all the cells that results in a character in that cell and then use conditional formatting to format the cell. You can "hide" the characters by making them (in the conditional formatting) the same color as the background.
So, for example, if you have starting week numbers in column B and ...

1

there is all sorts wrong, unfortunately!
ADDRESS gives you the address of the cell, not its value... so you are comparing whether an address is bigger/smaller
You have both checks as >= rather than one greater and one less than
You have a mix of , and ; (but I guess that is probably just editing onto this site!)
To enter the formula:
go into cell ...

1

Use the following formula in the conditional formatting for Actual Arrival Time column cells
=IF(HOUR(C3)=HOUR(B3),IF(MINUTE(C3)-MINUTE(B3)>5,TRUE,FALSE),IF(HOUR(C3)>HOUR(B3),TRUE,FALSE))
where column C has actual times and column B schedules times
To set this up go to HOME > Conditional Formatting > New Rule > Select "Use a formula to determine which ...

1

The first posted answer is definitely correct: the solution is to use conditional formatting.
Beyond that, it gets a bit hard to understand.
So this answer may, at some level, be technically equivalent to Brian’s,
but with more of an emphasis on clarity; this answer:
Identifies the rows and columns in the text in addition to the image.
Displays dates in ...

1

I tried to do this yesterday - I don't believe you can achieve this with conditional formatting data bars, which can only apply to cells containing numerical values.
The way I got round it was to overlay a bar chart, removing all the backgrounds, borders, axes and gridlines, and setting the bar colour to be 60%+ transparent.

1

You will need a macro in VBA, like this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wk As Workbook
Set wk = ThisWorkbook
Dim ws As Worksheet
Set ws = ActiveSheet
CRow = Target.Row
CColumn = Target.Column
If CColumn = X Then
CellValue = Cells(CRow, CColumn)
Select Case CellValue
Case Is < 0
Cells(CRow, ...

1

This is similar to Gary’s Student’s approach. Define the following VBA function:
Function MyRowHidden(ref As Range)
MyRowHidden = Rows(ref.Row).Hidden
End Function
See How do I add VBA in MS Office?
if you need help with that. Now you can use MyRowHidden(cell)
to check whether the row containing cell is hidden.
The way I devised to solve the ...

1

Check if cell is blank AND if today is 14 days away from the date in b1.
Select the B column
Select conditional formatting > New Rule > Use a Formula...
=AND((B1-TODAY()<14),(NOT(ISBLANK(B1))))
Select Fill Style for red

1

I have tested this on Excel 2013:
[Green]"+"0.00%;[Red]"-"0.00%
NOTE: actually my excel is in another language with different decimal separator, so it looks like this:
[Green]"+"0,00%;[Red]"-"0,00%
but I assume yours use the dot as decimal.

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