Color Code Data According by Categories

[epoiezam]

Hi Guys:

Need your help here,

How to color code ZONE column according to their respective REGION
(as per sample below)



Region
Zone
Branch


North
North A
Branch NA1



North B
Branch NB1



North C
Branch NC1


West
West A
Branch WA1



West B
Branch WB1


East
East A
Branch EA1


South
South A
Branch SA1




South B
Branch SB1



South C
Branch SC1




Thank you in advance guys :)

[Paul_Hossler]

I've not that good with Conditional Formatting, but I think you can make it work.

I created the 4 Formula based rules using the first letter

16167

[epoiezam]

Thank you, really appreciate your effort.

But the above mention table is just a sample. The labels/ values will defer each time.
Anyway this can be done using VBA?
System read respective REGIONs then color code all ZONEs under them.
Can we use like xlup, xldown or loop.

[Paul_Hossler]

We can do all of that with more detailed requirements

1. You had NESW as regions. Are there more or different?

2. You need the color mapping of Region-to-Color

3. Does the Zone value start with 'North', etc.

4. Is the data sorted with the Region lines suppressed until they change?

It's easy enough to do a macro that takes your data and colors the font just like you have





Option Explicit
Sub ColorCode()
Dim colorToUse As Long
Dim iRow As Long

colorToUse = -1

Application.ScreenUpdating = False

With ActiveSheet
For iRow = 2 To .Cells(1, 1).CurrentRegion.Rows.Count
Select Case .Cells(iRow, 1).Value
Case "North"
colorToUse = vbRed
Case "West"
colorToUse = vbBlue
Case "East"
colorToUse = RGB(255, 128, 0) 'orange
Case "South"
colorToUse = vbGreen
End Select

If colorToUse <> -1 Then .Cells(iRow, 2).Font.Color = colorToUse

Next iRow
End With
Application.ScreenUpdating = False

End Sub

[epoiezam]

Thanks Paul,
My reply in Blue.

1. You had NESW as regions. Are there more or different? Currently we have 8 regions, 200++ Zones & 1000++ branches

2. You need the color mapping of Region-to-Color? No, just Zone

3. Does the Zone value start with 'North', etc. No. Sample Perak, Kuantan, Section 18 etc

4. Is the data sorted with the Region lines suppressed until they change? The data already when through some VBA processes.
(Sample copy of the original data below)

It's easy enough to do a macro that takes your data and colors the font just like you have




Cus_No
Segment
STICKINESS
AGE
AGE_GRP
NO_PROD
PROD_HOLD
CNT_CUS
TFA_MAY15
IA_MAY15
OTC
RANK_IND
engagement_type
exclusion_ind
TAC_Contact
ATM_Contact
CC_Contact
WOLOC_Contact
GCIF_CONTACT_1
GCIF_CONTACT_2
GCIF_CONTACT_3
GCIF_CONTACT_4
GCIF_CONTACT_5
CONTACT_OFCHOME_1
CONTACT_OFCHOME_2
CONTACT_OFCHOME_3
CONTACT_OFCHOME_4
CONTACT_OFCHOME_5
SEG_201507
PFNO
Sales_Name
Center
pstion
REGION
ENGAGEMENT_TIMES


8202471
AFF
Hardcore
38
AGE 31-40
5
4 OR 5
1
387502.5
379752.9
NON-OTC
12
2.Every 3 months
0
0122259220
012 2259220
?
?
0600122259220
600122259220
?
?
?
074317521
074317521
?
?
?
AFF
94149
Yap Li Ping
PWC Bdr. Puteri Puchong
RM
SELANGOR/NS
1


7759395
AFF
Hardcore
54
AGE 51-60
2
LESS THAN 4
1
437404.4
422924.3
NON-OTC
8
2.Every 3 months
0
?
?
?
?
600123088667
?
?
?
?
0351220304
0351220041
?
?
?
AFF
94149
Yap Li Ping
PWC Bdr. Puteri Puchong
RM
SELANGOR/NS
1


11887497
AFF
Hardcore
46
AGE 41-50
5
4 OR 5
1
360543.3
347827.6
NON-OTC
12
2.Every 3 months
0
0193215196
?
?
?
0600193215196
?
?
?
?
0380688051
0380601051
?
?
?
AFF
94149
Yap Li Ping
PWC Bdr. Puteri Puchong
RM
SELANGOR/NS
1

[PAB]

1. You had NESW as regions. Are there more or different? Currently we have 8 regions, 200++ Zones & 1000++ branches

2. You need the color mapping of Region-to-Color? No, just Zone

Hi epoiezam, don't you mean Zone to Region?

[Paul_Hossler]

Currently we have 8 regions, 200++ Zones & 1000++ branches

Your sample doesn't have a 'Zone' column

If you do mean 'Zone' that's 200++ colors

If you do mean 'Region' that's 8 colors. So again, you still need to identify which Reqion gets what color

[SamT]

Compiles, but not tested

Use only one of these two functions

Public Function ToggleColor() As Integer
'Used for alternating two ColorIndex Values
Static StateValue As Boolean

Const Color1 As Integer = 3 'set to desired ColorIndex
Const Color2 As Integer = 42

StateValue = Not StateValue
If StateValue Then
ToggleColor = Color1
Else: ToggleColor = Color2
End If

End Function
'______________________________________________________

Public Function CycleColors() As Integer
'Used to cycle thru several ColorIndexes
Static StateValue As Integer

'Cycle StateValue
StateValue = StateValue + 1
'recycle if > max number of colors. Max =4 for this example
If StateValue = 5 Then StateValue = 1

'set the ColorIndex value of CycleColors. Adjust values as desired
Select Case StateValue
Case 1: CycleColors = 3
Case 2: CycleColors = 42
Case 3: CycleColors = 7
Case 4: CycleColors = 17
End Select

End Function


Sub colorByZone()
Dim r As Long
Dim MyInterior As Integer

With Sheets("Sheet1") 'adjust as needed
For i = 2 To .Cells(Rows.Count, "A").End(xlUp).Row 'Adjust as needed
'Use only one of below
MyInterior = ToggleColor
MyInterior = CycleColors

.Cells(i, "A").Offset(, 1).Interior.ColorIndex = MyInterior 'adjust offset as needed
Do While .Cells(i, "A") = .Cells(i + 1, "A")
Cells(i + 1, "A").Offset(, 1).Interior.ColorIndex = MyInterior
i = i + 1
Loop
Next i
End With
End Sub