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.
- Click on the "Conditional Formatting" button.
-
Click on "New Rule..."
- Type the formula and then click on "Format..." button
-
Click on tab "Fill"
- Pick a color
- Click OK
- Click OK button
Download Excel *.xlsx file
Pamela asks: I would like to ask you how to identify PAIR of same numbers, but with a different sign. [โฆ]
Sankalp asks: I am working in a railway project as planner. How can i create a dynamic strip chart in [โฆ]
Highlight overlapping date ranges using conditional formatting
The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that [โฆ]
Highlight records based on overlapping date ranges and a condition
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it [โฆ]
Highlight dates in a date range
Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column [โฆ]
Lookup multiple values across columns and return a single value
This article demonstrates how to get a value from a dataset based on multiple conditions across multiple columns. S.Babu asks: [โฆ]
Filter shared records from two tables
I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in [โฆ]
Prevent duplicate records [Data Validation]
Debra Dalgleish demonstrated last week how to block duplicate entries in excel table. I made a comment and I thought I would share [โฆ]
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.
English
Spanish
