Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 247
Default Conditional Format: NOT Hidden Row, Not Locked Cell, No Data in Ce

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).R ow

' 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
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can the conditional format box be locked to the cell petite pied Excel Worksheet Functions 1 July 19th 07 09:39 AM
Paste text into a locked or hidden cell sparx Excel Discussion (Misc queries) 2 March 19th 06 10:38 AM
Cell Format Changes When Data Is Entered - Not Conditional Formatt SundanceKidLudwig Excel Worksheet Functions 2 September 30th 05 02:07 PM
Check cell contents then format as locked Katherine Excel Programming 3 February 1st 05 12:48 PM
conditional locked cell? choice[_2_] Excel Programming 1 September 10th 04 01:18 AM


All times are GMT +1. The time now is 02:44 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"