How to hard code a range of cells to be filled with a background color?

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
4,546
Office Version
  1. 2007
Platform
  1. Windows
I can't believe I that I can't figure this seemingly simple goal of always having a certain range of cells set to a background color. I have tried formatting the range via right clicking the highlighted range and then 'Format cells\fill'. This seems to work, just until I rerun the script, that is when the colored cell ranges change from what I set it up to originally.

Example Goal:

HardCodedRangeFillColorTestV1.xlsm
ABCDEFGHIJKLMNOPQRSTUVW
1ColourQty Ordered
2Black14182
3Blue342Red146Green83Blue13Brown410Black25
4Brown4101Blue21Purple27Red42Green61
5Green1963Green52Orange61Orange47
6Orange1082Red05Purple83
7Purple10102Yellow49
8Red18133Black1213
9Yellow491
Sheet1


Wrong result that occurs after rerunning the same script:

HardCodedRangeFillColorTestV1.xlsm
ABCDEFGHIJKLMNOPQRSTUVW
1ColourQty Ordered
2Black14182
3Blue342Red146Green83Blue13Brown410Black25
4Brown4101Blue21Purple27Red42Green61
5Green1963Green52Orange61Orange47
6Orange1082Red05Purple83
7Purple10102Yellow49
8Red18133Black1213
9Yellow491
Sheet1


How do I keep the initial range of colored cells from changing? The data displayed hasn't changed, so why are the colored cell ranges changing?
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
and the code you are using is ????
 
Upvote 0
and the code you are using is ????
Thank you @Michael M for your response. The macro is not color change oriented at all, if that is what you were suspecting.

VBA Code:
Sub JonnyL2()
   Dim Cl As Range
   Dim Tmp As Variant
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("E3:W8").SpecialCells(xlConstants, xlTextValues)
         If Not .Exists(Cl.Value) Then
            .Item(Cl.Value) = Array(Cl.Offset(, 1).Value, Cl.Offset(, 2).Value, 1)
         Else
            Tmp = .Item(Cl.Value)
            Tmp(0) = Tmp(0) + Cl.Offset(, 1).Value
            Tmp(1) = Tmp(1) + Cl.Offset(, 2).Value
            Tmp(2) = Tmp(2) + 1
            .Item(Cl.Value) = Tmp
         End If
      Next Cl
      Range("A2").Resize(.Count, 1).Value = Application.Transpose(.Keys)
      Range("B2").Resize(.Count, 3).Value = Application.Index(.Items, 0)
      Range("A1").Resize(.Count, 4).Sort Range("A1"), xlAscending, , , , , , xlYes
   End With
End Sub
 
Upvote 0
So this line throws it...
Range("A1").Resize(.Count, 4).Sort Range("A1"), xlAscending, , , , , xlYes
But after 3 runs it's back to normal !!
 
Upvote 0
So this line throws it...
Range("A1").Resize(.Count, 4).Sort Range("A1"), xlAscending, , , , , xlYes
But after 3 runs it's back to normal !!
Sorry @Michael M, I am a bit confused by your response.

I just now tested your theory of rerunning the script several times, I am not sure how that answers my original question though. It actually took me 5 times to get it back to the original range of colored cells, but when I ran it again, right back to the wrong ranges of colored cells.

That being said, I am still wondering how to answer my original question.
 
Upvote 0
apologies....I too, am confused....not by you, but the code result doesn't make sense.
I will continue to play with it, unless someone else steps in.
 
Upvote 0
So this line throws it...
Range("A1").Resize(.Count, 4).Sort Range("A1"), xlAscending, , , , , xlYes
I agree with that. Each time that line is triggered, it seems that the ranges of colored cells changes.

As I originally stated, I just don't get what is making this so complicated. LOL
 
Upvote 0
There has to be a better solution than this:

VBA Code:
Sub JonnyL2()
'
   Dim Cl As Range
   Dim Tmp As Variant
'
    For StupidMaintainCellRangeWithColorWorkAround = 1 To 5                                                         <----
        With CreateObject("scripting.dictionary")
            For Each Cl In Range("E3:W8").SpecialCells(xlConstants, xlTextValues)
                If Not .Exists(Cl.Value) Then
                    .Item(Cl.Value) = Array(Cl.Offset(, 1).Value, Cl.Offset(, 2).Value, 1)
                Else
                    Tmp = .Item(Cl.Value)
                    Tmp(0) = Tmp(0) + Cl.Offset(, 1).Value
                    Tmp(1) = Tmp(1) + Cl.Offset(, 2).Value
                    Tmp(2) = Tmp(2) + 1
                    .Item(Cl.Value) = Tmp
                End If
            Next Cl
'
            Range("A2").Resize(.Count, 1).Value = Application.Transpose(.Keys)
            Range("B2").Resize(.Count, 3).Value = Application.Index(.Items, 0)
            Range("A2").Resize(.Count, 4).Sort Range("A2"), xlAscending, , , , , , xlNo
        End With
    Next StupidMaintainCellRangeWithColorWorkAround                                                                 <----
End Sub

Man this is ticking me off.
 
Last edited:
Upvote 0
Why not just reset the colour at the end of the code
VBA Code:
Range("A:D").Interior.Color = xlNone
Range("A2:D4").Interior.Color = rgbGreen
 
Upvote 0
Solution

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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
Back
Top