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
Highlight cells based on coordinates.xlsx
Weekly Blog EMAIL
[newsletter_signup_form id=1]
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
Have you ever tried to build a formula to calculate discounts based on price? The VLOOKUP function is much easier […]
Use slicers to quickly filter chart data
Slicers let you control data displayed in a chart, simply press with left mouse button on a button to quickly […]
This article describes how to filter duplicate rows with the use of a formula. It is, in fact, an array […]
List permutations with repetition [UDF]
This blog post demonstrates a custom function (UDF) that creates permutations. Repetition is allowed. The custom function lets you specify the […]
How to create a combined stacked area and a clustered column chart
Combine the stacked area chart and the clustered column chart if you want to differentiate at least two data series to […]
Partial match for multiple strings – AND logic
This article demonstrates formulas that let you perform partial matches based on multiple strings and return those strings if all […]
Pamela asks: I would like to ask you how to identify PAIR of same numbers, but with a different sign. […]
This article demonstrates a Conditional Formatting formula that lets you highlight cells based on numerical ranges specified in an Excel […]
The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
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.
