excel vba change cell color based on tabs color

kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
363
Office Version
  1. 2019
Hello All,

assuming i have 20 data in range A2:A21 at sheet 1 and they are same as the tabs name
i need a vba to return same color index (3, 5 and 6) in column A if tabs color was changed

example: when tabs name(220408120732319399) was changed to colorindex 5, i want cell A9(220408120732319399) change to colorindex 5 as well

thank you very much
 

kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
363
Office Version
  1. 2019
Hi mumps
thank you very much for your reply

i have uploaded and sincerely thank you for your help
Book1.xlsm
Hi mumps
thank you very much for your reply
i use this book1.xlsm BUT i found, these sheets should be under same color which is not same as yours. :eek::eek:
example: 220408120732319399, 220408120742883325, 220408120748876347, 220408120748233625, 220408120757583658
ps. i use excel 2010

This is what I get using the file you uploaded:
kelvin.xlsm
ABC
1Order ID#StoreOrder Time
2220408120748876347LIMITED2022-04-08 12:07:48
3220408120831247369LIMITED2022-04-08 12:08:31
4220408120846858130LIMITED2022-04-08 12:08:46
5220408121233898363LIMITED2022-04-08 12:12:33
6220408121734463603LIMITED2022-04-08 12:17:34
7220408121806702374LIMITED2022-04-08 12:18:06
8220408121814839240LIMITED2022-04-08 12:18:14
9220408121836385762LIMITED2022-04-08 12:18:36
10220408121931737641LIMITED2022-04-08 12:19:31
11220408121935236363LIMITED2022-04-08 12:19:35
12220408121941224563LIMITED2022-04-08 12:19:41
13220408122549172212LIMITED2022-04-08 12:25:49
14220408132755906161LIMITED2022-04-08 13:27:55
15220408120732319399LIMITED2022-04-08 12:07:32
16220408120732997586LIMITED2022-04-08 12:07:32
17220408120742883325LIMITED2022-04-08 12:07:42
18220408120748233625LIMITED2022-04-08 12:07:48
19220408120757583658LIMITED2022-04-08 12:07:57
20220408132726693238LIMITED2022-04-08 13:27:26
21220408014059601495NORMAL2022-04-08 01:40:59
22220408082500623556NORMAL2022-04-08 08:25:00
23220408094403852836NORMAL2022-04-08 09:44:03
24220408104323972983NORMAL2022-04-08 10:43:23
25220408105352392249NORMAL2022-04-08 10:53:52
26220408110203623574NORMAL2022-04-08 11:02:03
27220408110255197474LIMITED2022-04-08 11:02:55
28220408110255571326LIMITED2022-04-08 11:02:55
29220408110314288956LIMITED2022-04-08 11:03:14
30220408110324446526LIMITED2022-04-08 11:03:24
31220408110333240110LIMITED2022-04-08 11:03:33
32220408110338272581LIMITED2022-04-08 11:03:38
33220408110344507460LIMITED2022-04-08 11:03:44
34220408110355347470LIMITED2022-04-08 11:03:55
35220408110356525928LIMITED2022-04-08 11:03:56
36220408110403648543LIMITED2022-04-08 11:04:03
37220408110415585878LIMITED2022-04-08 11:04:15
38220408110419433895LIMITED2022-04-08 11:04:19
39220408110421921584LIMITED2022-04-08 11:04:21
40220408110435129990LIMITED2022-04-08 11:04:35
41220408110440149740LIMITED2022-04-08 11:04:40
42220408110443682411LIMITED2022-04-08 11:04:43
43220408110446744362LIMITED2022-04-08 11:04:46
44220408110459919369LIMITED2022-04-08 11:04:59
45220408110501416297LIMITED2022-04-08 11:05:00
46220408110503372426LIMITED2022-04-08 11:05:03
SUMMARY

Are you using the macro in a different workbook?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,328
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Don't worry about the colors because I colored some of the tabs at random to test the macro so they will be different from your workbook. Is the result you are getting the one you want for your workbook?
 

kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
363
Office Version
  1. 2019
Don't worry about the colors because I colored some of the tabs at random to test the macro so they will be different from your workbook. Is the result you are getting the one you want for your workbook?
Hi mumps
thank you very much for your reply

unfortunately no,
i use book1.xlsm run and i get below result again
ps1. i use your revised code at post #28, i will try in another computer and excel version(maybe 2016) see any different today
ps2. i start a new workbook with some order SN, random colored as you, change the sheet name as "summary", run the code, all go black

Order ID#
220408121806702374
220408121814839240
220408121836385762
220408121931737641
220408121935236363
220408121941224563
220408120732319399
220408120732997586
220408120742883325
220408120748876347
220408120748233625
220408120757583658
220408120831247369
220408120846858130
220408121233898363
220408121734463603
220408122549172212
220408132726693238
220408132755906161
220408014059601495
220408082500623556
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,328
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
The result you showed is exactly what you asked for: white on top, then colored and black at the bottom.
 

kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
363
Office Version
  1. 2019
The result you showed is exactly what you asked for: white on top, then colored and black at the bottom.
Hi mumps
thank you very much for your reply

i tried another computer with excel 2016 at office today, same result returned
however, can i sort other colored together too then?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,328
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
The macro is designed to place white cells on top, next all colored cells and then black cells at the bottom.
 

kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
363
Office Version
  1. 2019
The macro is designed to place white cells on top, next all colored cells and then black cells at the bottom.
Hi mumps
thank you very much for your reply

which means i need another macro?
can i do this way?

Order ID#
220408110255571326
220408110314288956
220408110324446526
220408110333240110
220408110338272581
220408110344507460
220408110355347470
220408110356525928
220408110403648543
220408110415585878
220408110419433895
220408110421921584
220408110435129990
220408110440149740
220408110443682411
220408110446744362
220408110459919369
220408110501416297


thank you very much?‍♂️?‍♂️?‍♂️
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,328
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Try:
VBA Code:
Sub ColorCells2()
    Application.ScreenUpdating = False
    Dim v As Variant, desWS As Worksheet, i As Long, lRow As Long, dic As Object, k As Variant
    Set desWS = Sheets("SUMMARY")
    With desWS
        lRow = .Range("A" & .Rows.count).End(xlUp).Row
        v = .Range("A2:A" & lRow).Value
        For i = LBound(v) To UBound(v)
            If Evaluate("isref('" & CStr(v(i, 1)) & "'!A1)") Then
                If Sheets(CStr(v(i, 1))).Tab.Color <> 0 Then
                    .Range("A" & i + 1).Interior.Color = Sheets(CStr(v(i, 1))).Tab.Color
                End If
            Else
                .Range("A" & i + 1).Interior.Color = vbBlack
            End If
        Next i
    End With
    Set dic = CreateObject("Scripting.Dictionary")
    For i = 1 To Sheets.count
        If Sheets(i).Name <> "PERSONALIZATION" And Sheets(i).Name <> "SUMMARY" Then
            If Sheets(i).Tab.Color <> False Then
                If Not dic.exists(Sheets(i).Tab.Color) Then
                    dic.Add Sheets(i).Tab.Color, Nothing
                End If
            End If
        End If
    Next i
    For Each k In dic.keys
        With Sheets("SUMMARY").Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range("A2:A888"), SortOn:=xlSortOnCellColor, Order:=xlAscending, DataOption:=xlSortNormal
            .SortFields.Add(Range("A2:A888"), xlSortOnCellColor, xlDescending, , xlSortNormal).SortOnValue.Color = RGB(0, 0, 0)
            .SortFields.Add(Range("A2:A888"), xlSortOnCellColor, xlDescending, , xlSortNormal).SortOnValue.Color = k
            .SetRange Range("A1:Y888")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    Next k
    Application.ScreenUpdating = True
End Sub
 

kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
363
Office Version
  1. 2019
Try:
VBA Code:
Sub ColorCells2()
    Application.ScreenUpdating = False
    Dim v As Variant, desWS As Worksheet, i As Long, lRow As Long, dic As Object, k As Variant
    Set desWS = Sheets("SUMMARY")
    With desWS
        lRow = .Range("A" & .Rows.count).End(xlUp).Row
        v = .Range("A2:A" & lRow).Value
        For i = LBound(v) To UBound(v)
            If Evaluate("isref('" & CStr(v(i, 1)) & "'!A1)") Then
                If Sheets(CStr(v(i, 1))).Tab.Color <> 0 Then
                    .Range("A" & i + 1).Interior.Color = Sheets(CStr(v(i, 1))).Tab.Color
                End If
            Else
                .Range("A" & i + 1).Interior.Color = vbBlack
            End If
        Next i
    End With
    Set dic = CreateObject("Scripting.Dictionary")
    For i = 1 To Sheets.count
        If Sheets(i).Name <> "PERSONALIZATION" And Sheets(i).Name <> "SUMMARY" Then
            If Sheets(i).Tab.Color <> False Then
                If Not dic.exists(Sheets(i).Tab.Color) Then
                    dic.Add Sheets(i).Tab.Color, Nothing
                End If
            End If
        End If
    Next i
    For Each k In dic.keys
        With Sheets("SUMMARY").Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range("A2:A888"), SortOn:=xlSortOnCellColor, Order:=xlAscending, DataOption:=xlSortNormal
            .SortFields.Add(Range("A2:A888"), xlSortOnCellColor, xlDescending, , xlSortNormal).SortOnValue.Color = RGB(0, 0, 0)
            .SortFields.Add(Range("A2:A888"), xlSortOnCellColor, xlDescending, , xlSortNormal).SortOnValue.Color = k
            .SetRange Range("A1:Y888")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    Next k
    Application.ScreenUpdating = True
End Sub
Hi mumps
thank you very much for your reply

this is amazing and just doing what i want exactly
thank you very much for the great work

is this range(A2:A888) can be set to the last row of column A?
this seems "separated" one row because of the data at last row of column M:R, example on row 36

ORDER 1.1.xlsm
ABCDEFGHIJKLMNOPQR
1Order ID#StoreOrder TimeStatusCurrent Status Change DateLast Update TimeMA#E-mailMobile#Pickup LocationPOS Ref IDRemarksMA TypeQuantityTotalDelivery FeeDiscount TotalNet Amount
2220417011020372699NORMAL2022-04-17 01:10:20ORDER2022-04-17 01:14:442022-04-17 01:14:4451,206.000.00120.601,085.40
3220417014655948732NORMAL2022-04-17 01:46:55ORDER2022-04-17 01:48:402022-04-17 01:48:401109.000.000.00109.00
4220417025725708603NORMAL2022-04-17 02:57:25ORDER2022-04-17 02:59:052022-04-17 02:59:0561,105.000.00207.10897.90
5220417091829108504NORMAL2022-04-17 09:18:29ORDER2022-04-17 09:18:512022-04-17 09:18:511119.000.0023.8095.20
6220417094530106908LIMITED2022-04-17 09:45:30ORDER2022-04-17 09:48:002022-04-17 09:48:006914.000.00182.80731.20
7220417100210211855NORMAL2022-04-17 10:02:10ORDER2022-04-17 10:03:442022-04-17 10:03:442398.000.0079.60318.40
8220417104315559393NORMAL2022-04-17 10:43:15ORDER2022-04-17 10:45:112022-04-17 10:45:111179.000.0017.90161.10
9220417113642821719NORMAL2022-04-17 11:36:42ORDER2022-04-17 11:40:212022-04-17 11:40:213405.0050.000.00405.00
10220417115501183122LIMITED2022-04-17 11:55:01ORDER2022-04-17 11:55:352022-04-17 11:55:351159.000.0031.80127.20
11220417120217484623NORMAL2022-04-17 12:02:17ORDER2022-04-17 12:03:242022-04-17 12:03:242218.000.0043.60174.40
12220417140329172855LIMITED2022-04-17 14:03:29ORDER2022-04-17 14:04:542022-04-17 14:04:545995.000.00199.00796.00
13220417144503535884NORMAL2022-04-17 14:45:03ORDER2022-04-17 14:46:402022-04-17 14:46:401799.000.0079.90719.10
14220417145617731763LIMITED2022-04-17 14:56:17ORDER2022-04-17 14:57:062022-04-17 14:57:061179.0050.000.00179.00
15220417150449485318NORMAL2022-04-17 15:04:49ORDER2022-04-17 15:05:332022-04-17 15:05:331119.000.0023.8095.20
16220417161105433274NORMAL2022-04-17 16:11:05ORDER2022-04-17 16:11:492022-04-17 16:11:492468.0050.000.00468.00
17220417162240821600NORMAL2022-04-17 16:22:40ORDER2022-04-17 16:23:272022-04-17 16:23:271599.000.00119.80479.20
18220417164648222747NORMAL2022-04-17 16:46:48ORDER2022-04-17 16:47:532022-04-17 16:47:5381,163.000.00116.301,046.70
19220417165712153277NORMAL2022-04-17 16:57:12ORDER2022-04-17 16:59:022022-04-17 16:59:021399.0050.000.00399.00
20220417172658997981NORMAL2022-04-17 17:26:58ORDER2022-04-17 17:27:542022-04-17 17:27:541799.000.00159.80639.20
21220417185939399859LIMITED2022-04-17 18:59:39ORDER2022-04-17 19:00:312022-04-17 19:00:315995.000.000.00995.00
22220417190209256234LIMITED2022-04-17 19:02:09ORDER2022-04-17 19:02:592022-04-17 19:02:595995.000.000.00995.00
23220417193640277926NORMAL2022-04-17 19:36:40ORDER2022-04-17 19:38:582022-04-17 19:38:582428.000.0042.80385.20
24220417194937142518LIMITED2022-04-17 19:49:37ORDER2022-04-17 19:50:382022-04-17 19:50:381129.000.0025.80103.20
25220417203309963832NORMAL2022-04-17 20:33:09ORDER2022-04-17 20:35:002022-04-17 20:35:0131,198.000.00239.60958.40
26220417210420667277NORMAL2022-04-17 21:04:20ORDER2022-04-17 21:06:182022-04-17 21:06:184956.000.000.00956.00
27220417211112364165NORMAL2022-04-17 21:11:12ORDER2022-04-17 21:14:322022-04-17 21:14:3251,056.000.00189.40866.60
28220417211442564912NORMAL2022-04-17 21:14:42ORDER2022-04-17 21:16:012022-04-17 21:16:01101,207.000.00241.40965.60
29220417220545506563NORMAL2022-04-17 22:05:45ORDER2022-04-17 22:06:462022-04-17 22:06:4661,645.000.00329.001,316.00
30220417223652193950NORMAL2022-04-17 22:36:52ORDER2022-04-17 22:37:452022-04-17 22:37:451179.0050.000.00179.00
31220417225312335497NORMAL2022-04-17 22:53:12ORDER2022-04-17 22:54:252022-04-17 22:54:251179.0050.000.00179.00
32220417233343440715LIMITED2022-04-17 23:33:43ORDER2022-04-17 23:34:572022-04-17 23:34:57305,020.000.001,004.004,016.00
33220417233754752407LIMITED2022-04-17 23:37:54ORDER2022-04-17 23:39:242022-04-17 23:39:24254,025.000.00805.003,220.00
34220417233757262994NORMAL2022-04-17 23:37:57ORDER2022-04-17 23:39:512022-04-17 23:39:5181,003.000.00200.60802.40
35220417234808101740LIMITED2022-04-17 23:48:08ORDER2022-04-17 23:49:192022-04-17 23:49:19305,020.000.001,004.004,016.00
3622139,203.00400.005,899.2033,303.80
37220417011901704829NORMAL2022-04-17 01:19:01ORDER2022-04-17 01:20:532022-04-17 01:20:534826.000.000.00826.00
38220417012353724955NORMAL2022-04-17 01:23:53ORDER2022-04-17 01:26:312022-04-17 01:26:31151,926.000.00182.801,743.20
39220417183001134884NORMAL2022-04-17 18:30:01ORDER2022-04-17 18:31:272022-04-17 18:31:277713.0050.00122.80590.20
40220417215249245486NORMAL2022-04-17 21:52:49ORDER2022-04-17 21:57:052022-04-17 21:57:051309.0050.000.00309.00
41220417232459807783NORMAL2022-04-17 23:24:59ORDER2022-04-17 23:26:362022-04-17 23:26:3691,062.000.00106.20955.80
SUMMARY
 

kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
363
Office Version
  1. 2019
just in case, this is the original data i have before i run the code
M41:R41 as this example

Order-Report (8).xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
1Order ID#StoreOrder TimeStatusCurrent Status Change DateLast Update TimeMA#E-mailMobile#Pickup LocationPOS Ref IDRemarksMA TypeQuantityTotalDelivery FeeDiscount TotalNet AmountDelivery First NameDelivery Last NameDelivery MobileDelivery Address 1Delivery Address 2RegionDistrict
39220417233757262994NORMAL2022-04-17 23:37:57ORDER2022-04-17 23:39:512022-04-17 23:39:51PLATINUM81,003.000.00200.60802.40
40220417234808101740LIMITED2022-04-17 23:48:08ORDER2022-04-17 23:49:192022-04-17 23:49:19PLATINUM305,020.000.001,004.004,016.00
41Total:22139,203.00400.005,899.2033,303.80
Worksheet
 

Forum statistics

Threads
1,182,103
Messages
5,933,657
Members
436,904
Latest member
DangerKennedy

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