Highlight cells based on coordinates
The picture above shows Conditional Formatting highlighting cells in cell range F3:Y22 based on row and column values in column B and C.
Conditional Formatting formula applied to cell range F3:Y22:
The first coordinate in cell range B3:C3 is column 1 and row 4, the conditional formatting formula highlights cell F6 because it is column 1 determined by the value in F2 and row 4 based on value in E6.
Explaining Conditional Formatting formula in cell F3
The COUNTIFS function counts the number of records in B3:C13 that match both the column (F$2) and row ($E3) value.
F$2 is 1 and $E3 is 1, no record in B3:C13 matches so cell F3 is not highlighted.
F$2 is locked to row 2 and $E3 is locked to column E so the cell references changes to F$2 and $E4 in next cell below. The dollar sign $ determines if a cell reference is absolute (locked) or relative.
How to apply conditional formatting formula to a cell range
- Select cell range F3:Y22.
- Go to tab "Home" on the ribbon.
- Press with mouse on the "Conditional Formatting" button.
- Press with mouse on "New Rule..."
- Type the formula and then press with left mouse button on "Format..." button
- Press with mouse on tab "Fill"
- Pick a color
- Press with left mouse button on OK
- Press with left mouse button on OK button
Get Excel *.xlsx file
Cf misc category
Here is how to highlight every other row using conditional formatting. Conditional formatting formula: =ISEVEN(ROW())*OR($B3:$D3<>"") Alternative CF formula: =EVEN(ROW())=ROW() This […]
Question: I have a list that I keep adding rows to. How do i create a border that expands as […]
This article demonstrates how to apply different cell formatting to a cell range based on a Drop Down list, column […]
Conditional formatting category
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
Functions in this article
More than 1300 Excel formulas
Conditional Formatting categories
Excel categories
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.