Yes I would like an uncolor function.
There are three subsets I'm looking to check. For simplicity let's refer to them as IR, MARKER and IR CREDIT:
IR - Master Sheet(L467:X531)
MARKER - Master Sheet(L71:X135)
IR CREDIT - Master Sheet(L1299:X1363)
For the code, I would want it to check the following in this order ANY time either the IR, MARKER, or IR CREDIT subset cell is touched:
If MARKER = "FC" Then COLOR = 3
If MARKER = "BC" Then COLOR = 45
If MARKER = "ADFEAT" Then COLOR = 4
If MARKER = "AD" Then COLOR = 6
If MARKER = "LL" Then COLOR = 40
If MARKER = "ROP" Then COLOR = 41
If MARKER = "AMD" Then COLOR = 26
If MARKER = "MB" Then COLOR = 31
If MARKER = "AAM" Then COLOR = 8
If IR.Value > 0 Then COLOR = 22
If None of the Above Then COLOR = 0
End If
If IR CREDIT.Value > 0 Then COLOR_REF2 = 12
If Not Then COLOR_REF2 = COLOR
Now we have two color variables checked, COLOR and COLOR_REF2:
I would like COLOR applied to the following Ranges if the one of the conditions above for the first row, first column cell of the subsets (L71,L467,L1299), holds true:
GROUP A: Master sheet - L5,L71,L137,L203,L269,L334,L401,L467,L533,L599,L669,L740,L812,L883,L954,L1025,L1096,L1167,L1233,L1299,L1365,L1436,L1507,L1573,L1644,L1715,L1786,L1857,L1928 = COLOR
GROUP B: Quickview sheet - C12:C28 = COLOR
GROUP C: Quickview sheet - C29:C29 = COLOR_REF2 & Font.Color = Red and Type = Bold
Moving on to check the second row first column cell of the subsets (L72,L468,L1300), if one of the conditions hold true the following cells should be colored:
GROUP A + 1 row (L6,L72,L138,etc.)= COLOR
GROUP B + (1*18) rows (C30:C46)=COLOR
GROUP C + (1*18) rows (C47:C47)= COLOR_REF2
Moving on to check the third row, first column cell of the subsets (L73,L469,L1301), if one of the conditions hold true the following cells should be colored:
GROUP A + 2 rows (L8,L74,L140,etc.)= COLOR
GROUP B + (2*18) rows (C48:C64)= COLOR
GROUP C + (2*18) rows(C65:C65)= COLOR_REF2
Now moving on to check fourth row, second column cell of the subsets (M74,M470,M1302), if one of hte conditions hold true the following cells should be colored:
GROUP A offset 1 column to the right and + 3 rows (M9,M75,M141,etc.)= COLOR
GROUP B offset 1 column to the right and + (3*18) rows (C66:C82)= COLOR
GROUP C offset 1 column to the right and + (3*18) rows (C83:C83)= COLOR_REF2
and so on. My original code in the original post had all of of this working but it was just too long.
A few other conditions:
Example.
L467 = "AD"
L71 = 20
L1299 = 10
The formatting for this should be color 6 for groups A & B and color 12,bold,red font for group C.
Now if I was to clear cell L1299, Group C color should change to color 6 and groups A & B should stay color 6
Now if I cleared cell L467, Groups A,B,C should all change to color 22
Now if I added the value 10 back to L1299, Groups A,B shoudl stay 22 but Group C should now be color 12 again.
Now if I clear L71 and L1299, Groups A,B,C formats should go back to no fill
Does this help clarify. Sorry for the long windedness.
Here is a link to a sample of the file on box.com. Hopefully this will help clarify...
http://www.box.com/s/finjk49crau4jxhqpe1b