Formula for Conditional Formatting in Tables

CrazyBritGuy

New Member
Joined
Dec 10, 2019
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
How do I write formulas rules that are based upon multiple columns and/or rows in a table?

Following is a simple example (my actual data has >1,000 rows and many columns) and my actual conditions are more complex.

When I insert/delete/reorder new rows/columns I do not want to update the conditional formatting formula so I'm using named ranges for the columns (i.e. =Table1[[#All],[Date]], etc. )

I need to highlight the value in Amt2 if it is not the same as Amt1. If Amt1 is empty or blank don't highlight Amt1.

1601317187790.png
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
695
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
In my experience, CF does extend when table grows and automatically resizes. Just use the same type of formula as you would in ranges.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,770
Office Version
  1. 365
Platform
  1. Windows
Cross posted Formula for Conditional Formatting in Tables - OzGrid Free Excel/VBA Help Forum

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,572
Messages
5,572,990
Members
412,493
Latest member
faram
Top