Odd Shading of Cells

A

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,564
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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
424
kevin9999
K
J
  • Question
VBA Macro for hiding and unhiding rows is rearranging cells
2
Replies
14
Views
931
dmt32
D
M
  • Question
Hide Rows based on cell with drop down values
Replies
1
Views
405
kevin9999
K
A
  • Question
After User Enters A Value (Validation List), Not Automatically Advancing To Next Cell As Per "Select"
Replies
1
Views
173
Ark68
A
Guna13
  • Question
Help to Optimize Macro Code for Faster Performance
Replies
0
Views
750
Guna13
Guna13

Forum statistics

Threads
1,214,833
Messages
6,121,867
Members
449,053
Latest member
Mesh

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