Optimizing application of conditional formatting to a large table, with formatting dependent on two other values

svendfj

Board Regular
Joined
Mar 19, 2011
Messages
99
Office Version
  1. 2016
Platform
  1. Windows
I am trying to find the most efficient way to apply Conditional Formatting to the three 'CF' columns in Table 2, below. These columns are conditionally formatted, based on looking up the corresponding value from the three columns in Table 1.

The following array formula placed - initially - in the cell with the red border works:
{=INDEX(Table13[Y/N],MATCH($E5,IF(Table13[ID]=G$3,Table13[A]),0))}

My question is, given that the table will be large, with upto1500 of these cells that need to be conditionally formatted, is this array formula the most efficient method? This is part of a VBA solution. The columns with the conditional formatting will be interleaved into an existing, large table - using VBA.

In summary my question is: given that the conditional formatting will be applied to a very large table, and each 'Y/N' value depends on both the corresponding 'A' and 'ID' values, what is the most efficient method for applying conditional formatting?

All suggestions greatly appreciated.


1615777210579.png
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
(IGNORE THIS - having investigated more, it looks more complicated than I first though!)

If I've understood correctly you don't need a complicated CF. If the coloured cells are already populated with Y or N then simply set the CF to depend on the value in the cell. Or if you want to use a formula use =B4="Y" etc.

Not sure how you're producing your Table 2, but I'd be doing so using a Pivot Table.

HTH
 
Last edited:
Upvote 0
Thank you pjmorris. Table 2 is created initially and separately - from other data. Then, I will interleave these additional columns, with each cell in these columns displaying a color dependent on the corresponding value in Table 1 ('Y': green, "N": red). For example, the cell in Table 2 with the red border will be conditionally formatted green (A="a-1" and ID="1" --> Y/N = "Y").

Thanks again.
 
Upvote 0
Assuming Table 2 origin is at A1, what, for example, would put a 'Y' in the highlighted cell B4?
 
Upvote 0
The array formula in my original post (for example) looks up the value in column 'Y/N' that corresponds to 'A' = "a-1" and 'ID' = "1", which is "Y".
 
Upvote 0
I _was_ curious what someone that works with VBA more might recommend. The most basic solution is often the best, and here that could mean just adding a column to Table 1, that combines the first two columns. That additional column is then used to do a Vlookup from Table 2 to get the desired result. This is what I did. Thanks again pjmorris for your time and interest.
 
Upvote 0
Solution

Forum statistics

Threads
1,212,933
Messages
6,110,756
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top