How do I get Cell Data (once worked)

[cyauch]

Last week I wrote the following to highlight an entire row based on it
data when the workbook was opened:


PRIVATE SUB WORKBOOK_OPEN()
' DISPLAY A MESSAGE LETTING USER KNOW THAT YOU SCIPTS ARE RUNNING
' SO IT MAY TAKE A MINUTE TO BE ABLE TO WORK IN WORKBOOK.
MSGBOX \"RUNNING SCRIPTS TO UP DATE THE SHEETS IN THE WORKBOOK.\"
VBINFORMATION, \"UPDATING WORKSHEETS\"






'################################################################
' THE FOLLOWING WILL UPDATE SHEET 6 (CABLE SCOPE LOG) AND CHECK
' IF TSO LOAD AND / OR CABLE SCOPE HAS BEEN COMPLETED. IF EITHER
' OR BOTH HAVE BEEN COMPLETED, THEN IT WILL HIGHLIGHT THE ROW THE
' APPROPRIATE COLOR.
DIM STARTROW AS INTEGER ' HOLDS LAST ROW OF OUR 'FREEZE' PANE
1 ON SHEET 6
DIM WS AS WORKSHEET


STARTROW = 4
SET WS = SHEET6 ' SET WS OBJECT TO THE SHEET WE WANT T
LOOK AT

CALL PROTECTTOGGLE(FALSE, WS) ' UNPROTECT THE SHEET SO UPDATIN
CAN BE DONE
CALL UPDATECABLESCOPE(WS) ' DO UPDATING OF SHEET
CALL PROTECTTOGGLE(TRUE, WS) ' UPDATING COMPLETED, PROTECT TH
SHEET AGAIN
END SUB

PRIVATE SUB CYCLECELLS(WS AS WORKSHEET, SELROW AS INTEGER, COLOR A
INTEGER)
' FUNCTION WILL CYCLE THRU EACH COLUMN OF A ROW AND SET TH
BACKGROUND
' COLOR TO THE APPROPRIATE COLOR. 2 = WHITE, 40 = ORANGE, 36
YELLOW
FOR LOOPCOL = 1 TO 22
WS.CELLS(SELROW, LOOPCOL).INTERIOR.COLORINDEX = COLOR
NEXT LOOPCOL
END SUB
FUNCTION PROTECTTOGGLE(PROTECTSTATUS AS BOOLEAN, WS AS WORKSHEET)
'PURPOSE - FUNCTION TO TOGGLE BETWEEN PROTECTION FOR DESIRED SHEET

DIM PASSWORD AS STRING ' CURRENT PASSWORD FOR SHEET
PASSWORD = \"EPLAN1\"

IF PROTECTSTATUS = TRUE THEN
WS.PROTECT PASSWORD
ELSE
WS.UNPROTECT PASSWORD
END IF
END FUNCTION
PRIVATE SUB UPDATECABLESCOPE(WS AS WORKSHEET)
' PURPOSE: WILL GO THRU EACH RECORD AND DETEMINE IF SELECTED ROW NEED
TO BE
' HIGHLIGHTED WITH ORANGE, YELLOW, OR DEFAUJLT WHITE BASED O
ENTRIES IN
' THE TSO LOADED AND ACTUAL SCOPE COMP FIELDS.

DIM NUMROWS AS INTEGER ' VARIABLE FOR NUMBER OF RECORDS O
SHEET
DIM NUMCOLS AS INTEGER ' NUMBER OF COLUMNS THAT WE WANT T
LOOK AT
DIM SELROW AS INTEGER ' CURRENT SELECTED ROW (VIA LOO
STRUCTURE)
DIM SELCOL AS INTEGER ' CURRENT SELECTED COLUMN (VIA LOO
STRUCTURE)
DIM LOOPCOL AS INTEGER ' LOOP STRUCTURE


' COUNT NUMBER OF ROWS IN CURRENT SHEET
NUMROWS = WS.USEDRANGE.ROWS.COUNT

' COUNT NUMBER OF COLUMNS IN CURRENT SHEET
NUMCOLS = WS.USEDRANGE.COLUMNS.COUNT


'********************************************************************
' START AT NEXT ROW AFTER LAST ROW OF 'FREEZE' PANE
' AND CYCLE THRU EACH RECORD CHECKING FOR TSO AND/OR CABLE
' SCOPE COMPLETE DATES.

FOR SELROW = (STARTROW) TO NUMROWS

' CHECK IF CABLE SCOPE COMPLETE DATE IS ENTERED, IF SO THEN
' THERE IS NO NEED TO CHECK FOR TSO COMPLETE SINCE WE NEE
THAT
' TO DO CABLE SCOP. IF CABLE SCOPE IS COMPLETE, THEN DRAWIN
IS
' READY TO BE RELEASE FOR FINAL ISSUE/FULL SIZE ORDER.

IF NOT (WS.CELLS(SELROW, 13).VALUE = \"\") THEN
' CYCLE THRU EACH COLUMN AND SET BACKGROUND COLOR
CALL CYCLECELLS(WS, SELROW, 40)


' IF CABLE SCOPE COMPLETE IS NOT DONE, BUT TSO LOAD IS, THE
SET
' THE APPROPRIATE BACKGROUND COLOR
ELSEIF NOT (WS.CELLS(SELROW, 7).VALUE = \"\") AN
(WS.CELLS(SELROW, 13).VALUE = \"\") THEN
' CYCLE THRU EACH COLUMN AND SET BACKGROUND COLOR
CALL CYCLECELLS(WS, SELROW, 36)

' CHECK THERE THE TSO AND CABLE SCOPE COMPLETE FIELDS AR
BLANK
' IF SO, SET THE BACKGROUND TO WHITE (DEFAULT)
ELSEIF (WS.CELLS(SELROW, 7).VALUE = \"\") AND (WS.CELLS(SELROW
13).VALUE = \"\") THEN
CALL CYCLECELLS(WS, SELROW, 2)
END IF
NEXT SELROW
END SU


Today, I was going to add some code to update another sheet in thi
workbook based on inofrmation from this sheet. It involved all th
cells that have dates in them. Each Monday morning when the workboo
is opened, I wanted it to search certain DATE columns and update th
numbers in another sheet. So, I formatted the columns that have date
in them from TEXT to DATE because I thought that it would be eaiser t
compare the date on Monday with any date before that.

However, when I run this, I now get the error Application-Defined or
Object Error" on this line:

IF NOT (WS.CELLS(SELROW, 13).VALUE = \"\") THEN


I have copied all the text in teh date columns to a notepad file,
reformatted the columns to TEXT, pasted the info back in, and still get
the error.

Can anyone help me figure out why now It won't work considering the
code is the way it was before I somehow mucked it up? And I will still
need to retaint he feature of highlighting each too. Thanks.

 

[Tom Ogilvy]

IF NOT (WS.CELLS(SELROW, 13).VALUE = \"\") THEN

Has a syntax error \"\"

If you are trying to check if the cell contains a \ then

if not (Instr(ws.Cells(selrow,13),"\")>0) then

If you are looking to see if the cell has \ <spc> \

if not (Instr(ws.Cells(selrow,13),"\ \")>0) then

if you want to check if a cell contains a date

if isdate(ws.Cells(selrow,13)) Then