PDA

View Full Version : Conditional Formatting for data groups



zest1
12-08-2005, 12:01 PM
Hi,

is possible to apply a common conditional formatting criteria to many separate data sets or groups, but for each group to use its own reference data that is part of that group? In other words, to have a list of many data sets, and each set having its own unique reference data as the comparative criteria, but applying the same conditional formatting to all the sets.

Example:
Ref1 contains the reference data for which to compare the data (A, B, C???) within that group to, and which to apply conditional formatting. Ref2 contains the second reference data to compare the data within that group to, the same for Ref3, and so on.

Ref1 298 | 130 | 163
A 9 | 228 | 384
B 172 | 90 | 12
C 41 | 312 | 149
D?
Ref2 213 | 37 | 263
A 59 | 228 | 84
B 172 | 90 | 151
C 41 | 312 | 89
D?
Ref3?
A
B
C
D...
Ref4?
?

So any data in groups falling within the range of the Ref data according to the conditional formatting criteia, will have that formatting applied, for example:
if the difference = 1 to 50, then Green;
else if the difference = 51-100, then Yellow;
else if the difference = 101-200, then Red;
else no color

Is there any way to do this, perhaps with a cell formula, lookup table, VBA, or ???

Thanks.

zest1
12-08-2005, 12:12 PM
the example didn't display very well - should be:

REF1 | 298 | 130 | 163
A | 9 | 228 | 384
B | 172 | 90 | 12
C | 41 | 312 | 149
D?

(hope this works)

shades
12-08-2005, 12:13 PM
Howdy. You would have to use VBA, if I understand properly.

But it would help if you could attach a sample (with sensitive data changed), that shows what you have, and then another sheet that shows what you expect it to look like. My initial thought is that you could set up a CFControl worksheet that would have the underlying crieteria for each data set, and then use VBA to reference those items on the CFControl worksheet.

zest1
12-08-2005, 12:13 PM
haha, doesn't accept multiple spaces

zest1
12-08-2005, 05:32 PM
Hi Rich,

I'm trying to learn if the limits of conditional formatting can be expanded beyond the mere 3 conditions for a whole worksheet, and into the realm of 'multi-set' conditional formatting. I don't have anything specific layed out, but have an idea for a future project and wondering whether it's possible and what the code would look like. That is, to have many separate data sets and display relationships within each data set against a primary comparative 'target(s)' within that group only, unrelated to all the other data sets, but each data set having its own comparative taget for its own group.

So, rather than having all the cells in a worksheet be formatted universally against the criteria pre-set in Cond. Formatting, can each of the separate data groups can be allowed to format conditionally using their own 'target cells' within each group as the criteria for the formatting. Thus, dynamic rather than static formatting.

For example, rather than having all cells in the worksheet with a value btwn 1-50 format to Green, have all cells that are from 1-50 AWAY FROM the 'target' cells in each group be Green. So one group may have a cell with a 'target cell' value of say 250, and any cell within that group with the value btwn 200-300 will be Green - and each data group having its own target values for the cells within that group.

The concept is much simpler than the explanation, haha.