Odd Shading of Cells

A

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,530
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have an interesting phenonomeon happening with my VBA based worksheet.

When I run a particular module of code that unhides several rows of a worksheet, revealing cells that are automatically populated and editable by the user, there are a few cells that are lightly highlighted in blue. This isn't to be misunderstood as background ... background shading goes righ to the 4 lines of the cell grid. This shading is a few pixels off the gridlines. Similarly, there are highlighted row numbers and column letters. The cells that are highlighted are a few of the cells that are locked for editting and are a result of a copied over formula. The highlighting goes away when you click on that cell.

Any idea what this could be? One could almost say something is identifying the locked cells of the worksheet.

Code: 
Sub populate_existing()
Dim wshmain As Worksheet
Dim wshcore As Worksheet
Dim CRID As Long
Dim vlrange As Range

Set wshmain = Worksheets("Main")
Set wshcore = Worksheets("CONTROL_1")
CRID = wshmain.Range("B14")
Set vlrange = wshcore.Range("A:DZ")

With wshmain
    Application.CutCopyMode = False
    .Range("L62").Copy
    With .Range("G26, N26, X25, AE25, AE27, AL26, K42, R42, Y42, AF42,  G47, N47, N49, G54, N53")
        .PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=False
        .Locked = True
    End With
        
    If .Range("I18") = "DR" Then
        .Rows("22:36").EntireRow.Hidden = False
        .Rows("37:52").EntireRow.Hidden = True
        .Rows("56:60").EntireRow.Hidden = False
            
        Application.EnableEvents = False
        'Grooming
            .Range("D24").Value = Application.VLookup(CRID, vlrange, 20, False)
            .Range("D25").Value = Application.VLookup(CRID, vlrange, 21, False)
            .Range("E25").Value = Application.VLookup(CRID, vlrange, 22, False)
            .Range("D26").Value = Application.VLookup(CRID, vlrange, 24, False)
            '.Range("G26").Value = Application.VLookup(CRID, vlrange, 25, False)
            With .Range("D24:G24, D25:D25, E25:G25, D26:F26")
                .Locked = False
                .MergeCells = True
            End With
        'Preparation
            .Range("K27").Value = Application.VLookup(CRID, vlrange, 26, False) 'type
            .Range("K24").Value = Application.VLookup(CRID, vlrange, 27, False) 'date
            .Range("K25").Value = Application.VLookup(CRID, vlrange, 28, False) 'qualifier
            .Range("L25").Value = Application.VLookup(CRID, vlrange, 29, False) 'time
            .Range("K26").Value = Application.VLookup(CRID, vlrange, 31, False) 'staff
            '.Range("N26").Value = Application.VLookup(CRID, vlrange, 32, False) 'crew
            With .Range("K24:N24, K25:K25, L25:N25, K26:M26, K27:N27")
                .Locked = False
                .MergeCells = True
            End With
        'Signature
            .Range("U24").Value = Application.VLookup(CRID, vlrange, 49, False) 'qualifier
            .Range("V24").Value = Application.VLookup(CRID, vlrange, 50, False) 'time
            .Range("U25").Value = Application.VLookup(CRID, vlrange, 52, False) 'name
            '.Range("X25").Value = Application.VLookup(CRID, vlrange, 53, False) 'crew
            With .Range("U24:U24, V24:X24, U25:W25")
                .Locked = False
                .MergeCells = True
            End With
        'Lights
            .Range("AB24").Value = Application.VLookup(CRID, vlrange, 119, False) 'on qualifier
            .Range("AC24").Value = Application.VLookup(CRID, vlrange, 54, False) 'on time
            .Range("AB25").Value = Application.VLookup(CRID, vlrange, 55, False) 'staff
            '.Range("AE25").Value = Application.VLookup(CRID, vlrange, 56, False) 'crew
            .Range("AB26").Value = Application.VLookup(CRID, vlrange, 121, False) 'off qualifier
            .Range("AC26").Value = Application.VLookup(CRID, vlrange, 57, False) 'off time
            .Range("AB27").Value = Application.VLookup(CRID, vlrange, 58, False) 'staff
            '.Range("AE27").Value = Application.VLookup(CRID, vlrange, 59, False) 'crew
            If .Range("AC24") <> "NA" Then
                With .Range("AB24:AE27")
                    .Locked = False
                    .Font.Color = black
                End With
            Else
                With .Range("AB24:AE27")
                    .Locked = True
                    .Font.Color = RGB(224, 224, 224)
                End With
            End If
            With .Range("AB24:AB24, AC24:AE24, AB25:AD25, AB26:AB26, AC26:AE26, AB27:AD27")
                .MergeCells = True
            End With
        'Close
            .Range("AI24").Value = Application.VLookup(CRID, vlrange, 124, False) 'date
            .Range("AI25").Value = Application.VLookup(CRID, vlrange, 60, False) 'qualifier
            .Range("AJ25").Value = Application.VLookup(CRID, vlrange, 61, False) 'time
            .Range("AI26").Value = Application.VLookup(CRID, vlrange, 63, False) 'staff
            '.Range("AL26").Value = Application.VLookup(CRID, vlrange, 64, False) 'crew
            With .Range("AI24:AL24, AI25:AI25, AJ25:AL25, AI26:AK26")
                .Locked = False
                .MergeCells = True
            End With
        'Setup
            .Range("K29").Value = Application.VLookup(CRID, vlrange, 33, False) 'base dist
            .Range("K30").Value = Application.VLookup(CRID, vlrange, 36, False) 'pitch dist
            .Range("K31").Value = Application.VLookup(CRID, vlrange, 39, False) 'batters box
            .Range("K32").Value = Application.VLookup(CRID, vlrange, 42, False) 'safe line
            .Range("K33").Value = Application.VLookup(CRID, vlrange, 40, False) 'coaches box
            .Range("P29").Value = Application.VLookup(CRID, vlrange, 34, False) 'safety base
            .Range("P30").Value = Application.VLookup(CRID, vlrange, 38, False) 'pitch circle
            .Range("P31").Value = Application.VLookup(CRID, vlrange, 35, False) 'strike mat
            .Range("P32").Value = Application.VLookup(CRID, vlrange, 41, False) 'runners line
            .Range("P33").Value = Application.VLookup(CRID, vlrange, 43, False) 'commit line
            .Range("K34").Value = Application.VLookup(CRID, vlrange, 47, False) 'other 1
            .Range("K35").Value = Application.VLookup(CRID, vlrange, 48, False) 'other 2
            With .Range("K29:L29, K30:L30, K31:K31, K32:K32, K33:K33, P29:P29, P30:P30, P31:P31, P32:P32, P33:Q33, K34:U34, K35:U35")
                .Locked = False
                .MergeCells = True
            End With
        
        If .Range("I18") = "DT" Then
           .Rows("22:44").EntireRow.Hidden = False
           .Rows("44:52").EntireRow.Hidden = True
           .Rows("56:60").EntireRow.Hidden = False
            
           'Reline 1
                .Range("I38").Value = Application.VLookup(CRID, vlrange, 70, False) 'type
                .Range("I39").Value = Application.VLookup(CRID, vlrange, 65, False) 'division
                .Range("I40").Value = Application.VLookup(CRID, vlrange, 66, False) 'time from
                .Range("I41").Value = Application.VLookup(CRID, vlrange, 68, False) 'time to
                .Range("I42").Value = Application.VLookup(CRID, vlrange, 71, False) 'staff
                '.Range("K42").Value = Application.VLookup(CRID, vlrange, 72, False) 'crew
                With .Range("I38:K38, I39:K39, I40:K40, I41:K41, I42:J42")
                    .Locked = False
                    .MergeCells = True
                End With
           'Reline 2
                .Range("P38").Value = Application.VLookup(CRID, vlrange, 78, False) 'type
                .Range("P39").Value = Application.VLookup(CRID, vlrange, 73, False) 'division
                .Range("P40").Value = Application.VLookup(CRID, vlrange, 74, False) 'time from
                .Range("P41").Value = Application.VLookup(CRID, vlrange, 76, False) 'time to
                .Range("P42").Value = Application.VLookup(CRID, vlrange, 79, False) 'staff
                '.Range("R42").Value = Application.VLookup(CRID, vlrange, 80, False) 'crew
                With .Range("P38:R38, P39:R39, P40:R40, P41:R41, P42:R42")
                    .Locked = False
                    .MergeCells = True
                End With
           'Reline 3
                .Range("W38").Value = Application.VLookup(CRID, vlrange, 86, False) 'type
                .Range("W39").Value = Application.VLookup(CRID, vlrange, 81, False) 'division
                .Range("W40").Value = Application.VLookup(CRID, vlrange, 82, False) 'time from
                .Range("W41").Value = Application.VLookup(CRID, vlrange, 84, False) 'time to
                .Range("W42").Value = Application.VLookup(CRID, vlrange, 87, False) 'staff
                '.Range("Y42").Value = Application.VLookup(CRID, vlrange, 88, False) 'crew
                With .Range("W38:Y38, W39:Y39, W40:Y40, W41:Y41, W42:Y42")
                    .Locked = False
                    .MergeCells = True
                End With
           'Reline 4
                .Range("AD38").Value = Application.VLookup(CRID, vlrange, 94, False) 'type
                .Range("AD39").Value = Application.VLookup(CRID, vlrange, 89, False) 'division
                .Range("AD40").Value = Application.VLookup(CRID, vlrange, 90, False) 'time from
                .Range("AD41").Value = Application.VLookup(CRID, vlrange, 92, False) 'time to
                .Range("AD42").Value = Application.VLookup(CRID, vlrange, 95, False) 'staff
                '.Range("AF42").Value = Application.VLookup(CRID, vlrange, 96, False) 'crew
                With .Range("AD38:AF38, AD39:AF39, AD40:AF40, AD41:AF41, AD42:AF42")
                    .Locked = False
                    .MergeCells = True
                End With
        End If
        
    ElseIf Left(.Range("I18"), 1) = "F" Then
        .Rows("44:50").EntireRow.Hidden = False
        .Rows("22:43").EntireRow.Hidden = True
        .Rows("51:55").EntireRow.Hidden = True
        .Rows("56:60").EntireRow.Hidden = False
            
        Application.EnableEvents = True
        'Signature
            .Range("D46").Value = Application.VLookup(CRID, vlrange, 49, False) 'qualifier
            .Range("E46").Value = Application.VLookup(CRID, vlrange, 50, False) 'time
            .Range("D47").Value = Application.VLookup(CRID, vlrange, 52, False) 'name
            '.Range("G47").Value = Application.VLookup(CRID, vlrange, 53, False) 'crew
            With .Range("D46:D46, E46:G46, D47:F47")
                .Locked = False
                .MergeCells = True
            End With
        'Lights
            .Range("K46").Value = Application.VLookup(CRID, vlrange, 119, False) 'on qualifier
            .Range("L46").Value = Application.VLookup(CRID, vlrange, 54, False) 'on time
            .Range("K47").Value = Application.VLookup(CRID, vlrange, 55, False) 'staff
            '.Range("N47").Value = Application.VLookup(CRID, vlrange, 56, False) 'crew
            .Range("K48").Value = Application.VLookup(CRID, vlrange, 121, False) 'off qualifier
            .Range("L48").Value = Application.VLookup(CRID, vlrange, 57, False) 'off time
            .Range("K49").Value = Application.VLookup(CRID, vlrange, 58, False) 'staff
            '.Range("N49").Value = Application.VLookup(CRID, vlrange, 59, False) 'crew
            If .Range("L46") <> "NA" Then
                With .Range("K46:N49")
                    .Locked = False
                    .Font.Color = black
                End With
            End If
            With .Range("K46:K46, L46:N46, K47:M47, K48:K48, L48:N48, K49:M49")
                .MergeCells = True
            End With
        'Data
            .Range("Q46").Value = Application.VLookup(CRID, vlrange, 44, False) 'field layout
            .Range("Q49").Value = Application.VLookup(CRID, vlrange, 45, False) 'goal size
            With .Range("P46:R46, P49:R49")
                .Locked = True
            End With
            
    Else
        .Rows("50:55").EntireRow.Hidden = False
        .Rows("22:49").EntireRow.Hidden = True
        
        'Preparation
            .Range("D52").Value = Application.VLookup(CRID, vlrange, 27, False) 'date
            .Range("D53").Value = Application.VLookup(CRID, vlrange, 28, False) 'qualifier
            .Range("E53").Value = Application.VLookup(CRID, vlrange, 29, False) 'time
            .Range("D54").Value = Application.VLookup(CRID, vlrange, 31, False) 'staff
            '.Range("G54").Value = Application.VLookup(CRID, vlrange, 32, False) 'crew
            With .Range("D52:G52, D53:D53, E53:G53, D54:F54")
                .Locked = False
                .MergeCells = True
            End With
        'Signature
            .Range("K52").Value = Application.VLookup(CRID, vlrange, 49, False) 'qualifier
            .Range("L52").Value = Application.VLookup(CRID, vlrange, 50, False) 'time
            .Range("L53").Value = Application.VLookup(CRID, vlrange, 52, False) 'name
            '.Range("N53").Value = Application.VLookup(CRID, vlrange, 53, False) 'crew
            With .Range("K52:K52, L52:N52, K53:M53")
                .Locked = False
                .MergeCells = True
            End With
    End If
End With
Application.EnableEvents = True
End Sub

The cells highlighted are: G26, N26, X25, AE25, AE27, AL26, K42, R42, Y42, AF42, G47, N47, N49, G54, and N53.
I wasn't able to take a snapshot of the shading, it didn't show up in the Snippit tool image. I'll try another application, or send a screenshot later.
 
Last edited:

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

J
  • Question
Code Clean Up.
Replies
1
Views
157
HaHoBe
HaHoBe
K
  • Question
Hide/Unhide Rows based on cell values
Replies
2
Views
867
Alex Blakenburg
A
Z
  • Question
change the value from 0 to 1 in a cell if these criterias are met
Replies
7
Views
159
Micron
M
A
  • Solved
Wrong Value In Trying To Determine Last Occupied Cell in A Column
2
Replies
13
Views
762
Alex Blakenburg
A
N
  • Question
Lock column ranges based on value in header row, loop through all
Replies
0
Views
249
Nic2
N

Forum statistics

Threads
1,195,644
Messages
6,010,891
Members
441,571
Latest member
stolenweasel

Share this page

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