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

svendfj

Board Regular
Joined
Mar 19, 2011
Messages
85
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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,990
Office Version
  1. 2016
Platform
  1. Windows
(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:

svendfj

Board Regular
Joined
Mar 19, 2011
Messages
85
Office Version
  1. 2016
Platform
  1. Windows
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.
 

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,990
Office Version
  1. 2016
Platform
  1. Windows
Assuming Table 2 origin is at A1, what, for example, would put a 'Y' in the highlighted cell B4?
 

svendfj

Board Regular
Joined
Mar 19, 2011
Messages
85
Office Version
  1. 2016
Platform
  1. Windows
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".
 

svendfj

Board Regular
Joined
Mar 19, 2011
Messages
85
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,266
Members
416,963
Latest member
samfuge

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
Top