I hope to attach the following to a custom menu.
To be run whenever the user thinks he has completed the Spread-Sheet.
I have another macro that will Hide all non-selected Rows which I will link
to this one.
Once this macro is run, the user can see at a glance (almost) if there are
any cells that he needs to enter data into yet.
I hope this description is clear enough.
Sub Missing()
Dim ws As Worksheet
Set ws = Worksheets("Pricing")
Dim ILastRow As Long, cell As Range
' Determine Last Row
ILastRow = ws.Range("B:B").SpecialCells(xlCellTypeLastCell).Row
' NOTE RANGE: C6 thru E, Last Row
For Each cell In ws.Range("C6:E" & ILastRow)
' 1 In Rows that are NOT Hidden (if possible to do this)
' (should make it run a lot faster)
' 2 Find Cells that are NOT Locked (Skip over Locked Cells)
' 3 Check if NOT Locked cells have Data in them
' If YES, go to next
' 4 If NO, insert Grid Pattern in Cell,
' Then Continue
If Row Hidden = False
If Cell Selection.Locked = False
If Cell Selection = ""
Then
.FormatConditions.Add Type:=xlExpression, Formula1:="=($B" & _
cell.Row & "=""*"")"
.Selection.Interior
.Pattern = xlGrid
.PatternColorIndex = 1
Next
ws.Range("C6").Select
End Sub
|