PC Review


Reply
Thread Tools Rate Thread

Conditional Format: NOT Hidden Row, Not Locked Cell, No Data in Ce

 
 
=?Utf-8?B?QkVFSkFZ?=
Guest
Posts: n/a
 
      14th Mar 2007
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

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
lock cell conditional format but not restrict data entry =?Utf-8?B?R0FN?= Microsoft Excel Worksheet Functions 1 18th Aug 2007 06:53 AM
Can the conditional format box be locked to the cell =?Utf-8?B?cGV0aXRlIHBpZWQ=?= Microsoft Excel Worksheet Functions 1 19th Jul 2007 09:39 AM
Paste text into a locked or hidden cell sparx Microsoft Excel Misc 2 19th Mar 2006 10:38 AM
Cell Format Changes When Data Is Entered - Not Conditional Formatt =?Utf-8?B?U3VuZGFuY2VLaWRMdWR3aWc=?= Microsoft Excel Worksheet Functions 2 30th Sep 2005 02:07 PM
Conditional Format to flag if anything is Hidden? =?Utf-8?B?VG9kZA==?= Microsoft Excel Worksheet Functions 4 23rd Jul 2004 06:55 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:29 AM.