View Full Version : Color Code Data According by Categories
epoiezam
05-12-2016, 02:43 AM
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
05-12-2016, 04:21 AM
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
05-12-2016, 05:57 PM
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
05-12-2016, 06:24 PM
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
05-12-2016, 07:38 PM
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
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
05-13-2016, 10:50 AM
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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.