Highlight cells equal to
Excel lets you easily highlight values based on a condition you specify, with a built-in formatting or a custom formatting.
How to apply Conditional Formatting to values equal a condition
- Select cell range containing values you want to highlight.
- Go to tab "Home" on the ribbon if you are not already there.
- Click "Conditional formatting" button.
- Click on "Highlight Cells Rules".
- Click "Equal to..."
- A dialog box appears that lets you specify the date condition and the formatting.
- Pick a prebuilt formatting or use a custom format to create a new one.
- Light red Fill with dark red text
- Yellow fill with dark yellow text
- Green Fill with dark green text
- Light red fill
- Red text
- Red border
- Custom format...
- Click OK button.
Highlight rows equal to a condition
You need to use a formula instead of the prebuilt ones in order to highlight the entire row if a cell in column B meets the condition in cell F4.
- Go to tab "Home" on the ribbon.
- Click the "Conditional Formatting" button.
- Click "New Rule.." to open a dialog box.
- Click "Use a formula to determine which cells to format".
- Type the formula. (See below).
- Click "Format..." button and choose a formatting.
- Click OK button twice.
Conditional formatting formula
Explaining conditional formatting formula
The CF formula changes from cell to cell depending on how you set it up, a cell reference may be absolute or relative.
The dollar sign makes a cell reference locked (absolute), however, a cell reference may also have two dollar signs locking both the column and row. This is the case with $F$4, the condition cell reference never changes.
Cell reference $B3 is only locked to column B, when the CF moves to cell C3 cell ref $B3 is still $B3. This highlights cell C3, see example image above. The next cell is D3 and $B3 is still $B3 highlighting cell D3.
Cell B4 is on the next row and $B3 changes to $B4, cell $B4 is not equal to the condition in cell F4 so this cell is not highlighted and so on.
Highlight unique/duplicates [Conditional formatting]
Excel has a few built-in conditional formatting features, one of them highlights values that have at least one duplicate. The […]
Highlight cells containing string [Conditional formatting]
Excel allows you to quickly highlight cells containing a given text string. How to apply Conditional Formatting Select cell range. […]
Highlight a date occuring [Conditional formatting]
Excel has a built-in feature that allows you to highlight dates if a given condition is met.The date conditions you […]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.