Color me blue

Topic

New Reply

Hi! I want to ask a ‘fun’ question.
I am running code to output queries result into Excel file.
All fields that are blank should be highlighted in let say blue color.
I expect one blank field per row/column. It should be colored.

I am running a macro AND VBA code, so it can be done in any format.

Thanks

Reply | Quote

Replies

What code are you using to ouptput the values?

If it only outputs certain places, and just leaves some cells blank, then I would start with changing the color of the the affected cells to blue, and when the code puts a value in a cell to remove the color.

Or do it opposite if you are removing entries (clear all and make blue when “blanked”)

Another option is at the end of hte code, select the blanks in the region of interest and turn them blue

Additionally you could use conditional formatting in the cells to make blank cells blue. This will work after the fact as well to make cells blue when values are deleted and remove the blue when values are added, all automatically.

Steve

Reply | Quote

Thanks, Steve!

Here is the deal…

I am going to be using following code to create an output in Excel:

With rst2
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open “[Report_Queries]”, cnn1
End With
strReportName = varCombo1 & “Recruitment Audit Report.xls”
Debug.Print strReportName
If Dir(strReportName) “” Then
‘Debug.Print strReportName
Kill (strReportName)
End If

‘Loop thru queries
Do While Not rst2.EOF

strTemp = rst2.Fields(“QueryName”)

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTemp, strReportName

Set cmd1 = Nothing

rst2.MoveNext
Loop
rst2.Close
—————————————————————————————————————–

Report_Queries is the table that containing queries to run.

When all said queries are ran – I have Excel file with tab-per-query.

It is normally

ID Field1 MissingValues

12_______ABC_______[_____]
15_______ABB_______[_____]
20_______AAA_______[_____]

I am only displaying it for people to see what they have to fix.
So in illustrated case Missing Values should have 3 cells blue. No removal afterwards, no data entry – just visual effect to ‘not to miss’!!!

Thanks so much

Reply | Quote

You need to use Automation to run Excel from Access. For example:

Code:

Dim xlApp As Object
Dim xlWbk As Object
Dim xlWsh As Object
Set xlApp = CreateObject(Class:="Excel.Application")
Set xlWbk = xlApp.Workbooks.Open(Filename:=strReportName)
For Each xlWsh In xlWbk.Worksheets
  xlWsh.UsedRange.FormatConditions.Add(Type:=1, _
	Operator:=3, Formula1:="=""""").Interior.ColorIndex = 34
Next xlWsh
xlWbk.Close SaveChanges:=True
xlApp.Quit
Set xlWsh = Nothing
Set xlWbk = Nothing
Set xlApp = Nothing

Reply | Quote

Hello, Hans! And thanks so much as always.

Reply | Quote

Only thing is how to have all colored cells separated with grid vs solid rectangular and fonts MS Sans Serif 10 autofit so all the columns are as wide as they should be? Thanks

Reply | Quote

Try

Code:

For Each xlWsh In xlWbk.Worksheets
  xlWsh.UsedRange.FormatConditions.Add Type:=1, _
	Operator:=3, Formula1:="="""""
  With xlWsh.UsedRange.FormatConditions(1).Borders
	.LineStyle = 1
	.Weight = 2
	.ColorIndex = -4105
  End With
  xlWsh.UsedRange.FormatConditions(1).Interior.ColorIndex = 34
Next xlWsh

Reply | Quote

YAHOO! THANKS A MILLION!!!!!!!!

Reply | Quote

Hi again…
I was trying on my own to have autofit for the columns, bold headers and I’ve not gotten far:

xlWsh.UsedRange.FormatConditions(1).Select
xlWbk.Rows (“1:1”)
selection.Font.Bold = True
cells.EntireColumn.AutoFit
xlWsh.Rows(“1:1”).RowHeight = 20.25
ActiveWorkbook.Save

It stoped me at xlWsh.UsedRange.FormatConditions(1).Select

What am I doing wrong, please, tell. Thanks

Reply | Quote

It stoped me at xlWsh.UsedRange.FormatConditions(1).Select

xlWsh.UsedRange.FormatConditions(1) is the first conditional format that you see in the Format > Conditional Formatting dialog box, you can’t select this.

Try something much more simple like
xlWsh.UsedRange.Columns.Autofit

Reply | Quote

Stuart has already posted the solution, but I wanted to point out that the lines

selection.Font.Bold = True
cells.EntireColumn.AutoFit

and

ActiveWorkbook.Save

will cause serious problems. Because they do not refer directly or indirectly to one of the Excel object variables used in the code, they will cause an extra, invisible instance of Excel to be created. This instance will remain in memory when the procedure ends, taking up computer meomory and probably locking the worksheet.
When using Automation, you should ALWAYS refer back to the object variables, for example

cells.EntireColumn.AutoFit > xlWsh.Columns.AutoFit

and

ActiveWorkbook.Save > xlWbk.Save

Reply | Quote

Thanks, Stuart!!!

Hans, I am so surprised! Many years ago I was using VBA a lot and one of my main problem was that Excel hang up and I was going to Task Manager to shot it down.
I searched everywhere and there were questions and no answers. Now I got it and it is so cool!!!

I want to ask – I am getting more formatting requests and I use to record macro and then alter a bit and it worked.
Nowadays I am recording and trying to adopt into the code you gave me and it doesn’t fit…

How do I find correct properties to reffer to when I need to
center the text in columns
inderline A row
coloring headers where I have between 2-5 cells in row A to color
etc…

Thanks so much for your help!

Reply | Quote

You can record a macro in Excel for that, then adapt the code as follows: a recorded macro will usually refer to the Selection, i.e. the cell(s) selected by the user.
In your code, replace Selection with a reference to a range on your worksheet, for example

Selection.EntireColumn.AutoFit

becomes

xlWsh.UsedRange.EntireColumn.AutoFit

and

Selection.Font.Bold = True

becomes

xlWsh.Range(“A1:F1”).Font.Bold = True

Reply | Quote

Thanks, I had tried. Some successfull!

I haved another brain teaser.

My queries are missing data in each column in different cells in Excel.

I was asked to summarize each field and give a summary per ID.

I took each table apart in Access. Wrote tons of queries and built summary table.

After I had seen how easily you colored those empty cells for me I have a crazy idea.

Is there a way (maybe) to have sum of the colored field (thus Formula:””””) by column and also by row for my Summary WKsheet?

ID__________Col________Col2
12__________we__________[]
13__________[]___________[]
14__________[]___________[]

Total:_______2____________3

Thanks

Reply | Quote

You can use the COUNTBLANK function for this: for example if you have data in A1:A10, you could enter the formula

=COUNTBLANK(A1:A10)

in A11. Using code this would be

xlWsh.Range(“A11”).Formula = “=COUNTBLANK(A1:A10)”

Similar for the count of blank cells in a row: say you have data in A2:H2. You could place the following formula in I2:

=COUNTBLANK(A2:H2)

Using code:

xlWsh.Range(“I2”).Formula = “=COUNTBLANK(A2:H2)”

Reply | Quote

OMG! It is almost doable???

One thing is that using your code I will be outputing 6 worksheets with data.
I need my totals per column.
Columns are same amount per WKsheet but but different for each.

One WKsheet will have 25 rows/5 columns, another one 2 rows/10 columns.

I need to use something like UsedRange.EntireColumn…

Thanks, I will research as well. I did not think it was possible. THANKS!!!

Reply | Quote

You can do something like the following:

Dim lngMaxRow As Long
Dim lngMaxCol As Long
Dim r As Long
Dim c As Long

lngMaxRow = xlWsh.Cells.Find(What:=”*”, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lngMaxCol = xlWsh.Cells.Find(What:=”*”, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

For r = 2 To lngMaxRow
xlWsh.Cells(r, lngMaxCol + 1).FormulaR1C1 = “=COUNTBLANK(RC1:RC” & lngMaxCol & “)”
Next r

For c = 1 To lngMaxCol
xlWsh.Cells(lngMaxRow +1, c).FormulaR1C1 = “=COUNTBLANK(R1C:R” & lngMaxRow & “C)”
Next c

Reply | Quote

It seems so cool, however using the previously written code:
For Each xlWsh In xlWbk.Worksheets
xlWsh.UsedRange.FormatConditions.Add Type:=1, _
Operator:=3, Formula1:=”=”””””
With xlWsh.UsedRange.FormatConditions(1).Borders
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
xlWsh.UsedRange.FormatConditions(1).Interior.ColorIndex = 34
Next xlWsh

I am trying to figure out where to insert additional code and getting ‘out og range’ or ‘object is not set’ errors.

Thanks, I am continuing to look for right place to insert it…

Reply | Quote

Somewhere between

For Each xlWsh In xlWbk.Worksheets

and

Next xlWsh

Reply | Quote

Good Morning, everyone!
Hans,
This is what I had done from the beginning. I had altered code placing it between

For Each xlWsh In xlWbk.Worksheets

and

Next xlWsh

and ran it and start getting ‘subscript out of range’
at
IngMaxRow = xlWsh.Cells.Find(What:=”*”, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Later wherever I was placing that additional code – I was getting the same error. Sorry for bugging you with same stuff and thanks

_______________________________________________

Private Sub Color_Cells()
Dim xlApp As Object
Dim xlWbk As Object
Dim xlWsh As Object

Dim lngMaxRow As Long
Dim lngMaxCol As Long
Dim r As Long
Dim c As Long

Set xlApp = CreateObject(Class:=”Excel.Application”)
Set xlWbk = xlApp.Workbooks.Open(FileName:=strReportName)

For Each xlWsh In xlWbk.Worksheets

xlWsh.UsedRange.FormatConditions.Add Type:=1, _
Operator:=3, Formula1:=”=”””””
With xlWsh.UsedRange.FormatConditions(1).Borders
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105

End With

xlWsh.Range(“A1:D1″).Font.Bold = True
xlWsh.UsedRange.Columns.AutoFit

xlWsh.UsedRange.FormatConditions(1).Interior.ColorIndex = 36

IngMaxRow = xlWsh.Cells.Find(What:=”*”, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lngMaxCol = xlWsh.Cells.Find(What:=”*”, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

For r = 2 To lngMaxRow
xlWsh.Cells(r, lngMaxCol + 1).FormulaR1C1 = “=COUNTBLANK(RC1:RC” & lngMaxCol & “)”
Next r

For c = 1 To lngMaxCol
xlWsh.Cells(lngMaxRow + 1, c).FormulaR1C1 = “=COUNTBLANK(R1C:R” & lngMaxRow & “C)”
Next c

Next xlWsh

xlWbk.Close SaveChanges:=True
xlApp.Quit
Set xlWsh = Nothing
Set xlWbk = Nothing
Set xlApp = Nothing

End Sub
_______________________________________

Reply | Quote

I forgot that you probably don’t have a reference to the Excel object library. Does it work if you change the lines

IngMaxRow = xlWsh.Cells.Find(What:=”*”, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lngMaxCol = xlWsh.Cells.Find(What:=”*”, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

to

IngMaxRow = xlWsh.Cells.Find(What:=”*”, SearchOrder:=1, SearchDirection:=2).Row
lngMaxCol = xlWsh.Cells.Find(What:=”*”, SearchOrder:=2, SearchDirection:=2).Column

Reply | Quote

Oh, thank you, thank you, thank you!
I had added refferences. That other way screwed up headers.
Hans, Thanks so much!!!

Reply | Quote

I will push my luck and ask…
how can I have a Grand Total where the last row meets the column?

_____________________________1
_____________________________2
_____________________________3
_____________________________1
_____________________________3
Columns totals: 1_2_3_1_3 Sum:10

Thanks so much

Reply | Quote

Try adding this line:

xlWsh.Cells(lngMaxRow + 1, lngMaxCol + 1).FormulaR1C1 = “=SUM(RC1:RC[-1])”

Reply | Quote

Good Day!

When I am counting blanks I am using COUNTBLANKS, what should I do to count column’s total when no blanks present?

Also I have a first column as IS or Name and column A don’t have to be totaled. I would like to insert word ‘total’ into it. Is there way? Thanks

Reply | Quote

You can use COUNTA to count the number of non-blank cells in a range.

I don’t understand your second question.

Reply | Quote

A1cell
ID_____________________________1
12_____________________________2
11_____________________________3
13_____________________________1
15_____________________________3
Totals:____1_2_3_1_3___Sum:10

I do not want total in ID column. I want a word “Total:” instead.

Thanks for the first one and thanks in general :-))

Reply | Quote

You could change the lines

For c = 1 To lngMaxCol
xlWsh.Cells(lngMaxRow + 1, c).FormulaR1C1 = “=COUNTBLANK(R1C:R” & lngMaxRow & “C)”
Next c

to

xlWsh.Cells(lngMaxRow + 1, 1) = “Total”
For c = 2 To lngMaxCol
xlWsh.Cells(lngMaxRow + 1, c).FormulaR1C1 = “=COUNTBLANK(R1C:R” & lngMaxRow & “C)”
Next c

(If you use another function than COUNTBLANK, modify the code accordingly)

Reply | Quote

Great! Thank you.

I think the last thing I need for this one is when I am having

For Each xlWsh In xlWbk.Worksheets

and

Next xlWsh

and I need to do

If xlWks.Name “FirstWksheet” then

*********
*********

else

If xlWks.Name=”FirstWksheet” then

********

End If
End If

__________________________________

However I place my IFs – I am getting an errors. Please, show me how to place it correctly. Thanks

Reply | Quote

You need to be consistent – either use xlWsh or xlWks, not both.

Try

Code:

For Each xlWsh In xlWbk.Worksheets
  If xlWsh.Name = "FirstWksheet" then
	' Code to execute for FirstWksheet
	...
  Else 
	' Code to execute for other sheets
	...
  End If
Next xlWsh

Reply | Quote

All is working, I am so greatfull as always.

I am trying to color lngMaxRow and lngMaxCol so my totals will be nicely ‘framed’. Please…help…?

Reply | Quote

You could use something like this:

xlWsh.Range(xlWsh.Cells(1, lngMaxCol + 1), xlWsh.Cells(lngMaxRow + 1, lngMaxCol + 1)).Interior.ColorIndex = 19
xlWsh.Range(xlWsh.Cells(lngMaxRow + 1, 1), xlWsh.Cells(lngMaxRow + 1, lngMaxCol + 1)).Interior.ColorIndex = 19

Reply | Quote

I have also done this:

xlWsh.Range(xlWsh.Cells(1, lngMaxCol + 1), xlWsh.Cells(lngMaxRow + 1, lngMaxCol + 1)).Interior.ColorIndex = 36
xlWsh.Range(xlWsh.Cells(1, lngMaxCol + 1), xlWsh.Cells(lngMaxRow + 1, lngMaxCol + 1)).Font.Bold = True
xlWsh.Range(xlWsh.Cells(lngMaxRow + 1, 1), xlWsh.Cells(lngMaxRow + 1, lngMaxCol + 1)).Interior.ColorIndex = 36
xlWsh.Range(xlWsh.Cells(lngMaxRow + 1, 1), xlWsh.Cells(lngMaxRow + 1, lngMaxCol + 1)).Font.Bold = True

IT WORKED!!!!!!!!!!!!

Thanks so much for this wonderful Report.
Best wishes and thank you.

Reply | Quote

Good Morning again

I had just gotten ane small but nasty request.

I am highlighting empty cells in workbook when data looking like this:

ID_________StartDate_______JobStatus__JobID_____StartDatePast
11_________5/1/2009_________Filled_____333_________________
12_________6/1/2009_________Open_____231________1/1/2009
23_________[______]_________Filled_____543_________________

For the ID 23 – StartDate should be highlighted (because is blank) – which it is and it is great.

NOW I need to highlight only those StartDatePast – where values are having JobStatus = Open.
________________________________________________________________________________
_

How can I redo this code:

xlWsh.UsedRange.FormatConditions.Add Type:=1, _
Operator:=3, Formula1:=”=”””””
With xlWsh.UsedRange.FormatConditions(1).Borders
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
xlWsh.UsedRange.FormatConditions(1).Interior.ColorIndex = 36

to all of a sudden stop looking for the blanks and start looking for the condition:
If value of the column C = ‘Open’ then color corresponding cell in column B (if not blank).

Thanks for looking into it.

Reply | Quote

Try this code:

Code:

With Intersect(xlWsh.UsedRange, xlWsh.Columns(2)).FormatConditions
  .Add Type:=xlExpression, Formula1:="=$C1=""Open"""
  .Item(1).Interior.ColorIndex = 36
End With

Reply | Quote

Is that a question? It’s hard to tell with your entire reply contained in a quote.

Reply | Quote

That was the question. Not sure why in quote window.

I said:

I have 5 worksheets and this ‘issue’ is only pertaining to one.

So I had tried code below and it did not change anything:

xlWsh.UsedRange.FormatConditions.Add Type:=1, _
Operator:=3, Formula1:=”=”””””
With xlWsh.UsedRange.FormatConditions(1).Borders
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
xlWsh.UsedRange.FormatConditions(1).Interior.ColorIndex = 36

End With

If xlWsh.Name = “Problem_WKsheet” Then

With Intersect(xlWsh.UsedRange, xlWsh.Columns(2)).FormatConditions
.Add Type:=xlExpression, Formula1:=”=$C1=””Open”””
.Item(1).Interior.ColorIndex = 36
End With

End If
___________

Sorry for the confusion.

Reply | Quote

Perhaps you can use this as “skeleton”:

If xlWsh.Name = “Problem_WKsheet” Then
…
Else
…
End If

Reply | Quote

Hans,

I am getting a message
Method Intersect of object Global has failed.

I could not insert new code
__________
With Intersect(xlWsh.UsedRange, xlWsh.Columns(2)).FormatConditions
.Add Type:=xlExpression, Formula1:=”=$C1=””Open”””
.Item(1).Interior.ColorIndex = 36
End With
___________

into an old code.

I have instead created new Private Sub and calling it from the end of the previous code.

I will paste a whole thing below but it is just in case you want to see.
[codebox]
Private Sub Color_Cells()

Dim xlApp As Object
Dim xlWbk As Object
Dim xlWsh As Object

Dim lngMaxRow As Long
Dim lngMaxCol As Long
Dim r As Long
Dim c As Long

Set xlApp = CreateObject(Class:=”Excel.Application”)
Set xlWbk = xlApp.Workbooks.Open(FileName:=strReportName)

‘_______________________________________________________________

For Each xlWsh In xlWbk.Worksheets

If xlWsh.Name = “aud_CT_Summary_Totals” Then

lngMaxRow = xlWsh.Cells.Find(What:=”*”, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lngMaxCol = xlWsh.Cells.Find(What:=”*”, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

xlWsh.Cells(lngMaxRow + 1, 1) = “Total:”

For c = 2 To lngMaxCol
xlWsh.Cells(lngMaxRow + 1, c).FormulaR1C1 = “=SUM(R1C:R” & lngMaxRow & “C)”
Next c

xlWsh.Cells(lngMaxCol + 1).FormulaR1C1 = “Total”

For r = 2 To lngMaxRow
xlWsh.Cells(r, lngMaxCol + 1).FormulaR1C1 = “=SUM(RC1:RC” & lngMaxCol & “)”
Next r

xlWsh.Cells(lngMaxRow + 1, lngMaxCol + 1).FormulaR1C1 = “=SUM(RC1:RC[-1])”

xlWsh.Range(xlWsh.Cells(1, lngMaxCol + 1), xlWsh.Cells(lngMaxRow + 1, lngMaxCol + 1)).Interior.ColorIndex = 36
xlWsh.Range(xlWsh.Cells(1, lngMaxCol + 1), xlWsh.Cells(lngMaxRow + 1, lngMaxCol + 1)).Font.Bold = True
xlWsh.Range(xlWsh.Cells(1, lngMaxCol + 1), xlWsh.Cells(lngMaxRow + 1, lngMaxCol + 1)).Borders.LineStyle = 1

xlWsh.Range(xlWsh.Cells(lngMaxRow + 1, 1), xlWsh.Cells(lngMaxRow + 1, lngMaxCol + 1)).Interior.ColorIndex = 36
xlWsh.Range(xlWsh.Cells(lngMaxRow + 1, 1), xlWsh.Cells(lngMaxRow + 1, lngMaxCol + 1)).Font.Bold = True
xlWsh.Range(xlWsh.Cells(lngMaxRow + 1, 1), xlWsh.Cells(lngMaxRow + 1, lngMaxCol + 1)).Borders.LineStyle = 1

xlWsh.Range(“A1:D1”).Font.Bold = True
xlWsh.UsedRange.Font.Size = 8.5
xlWsh.Columns(“B:C”).HorizontalAlignment = xlCenter
xlWsh.UsedRange.Columns.AutoFit

Else

xlWsh.UsedRange.FormatConditions.Add Type:=1, _
Operator:=3, Formula1:=”=”””””
With xlWsh.UsedRange.FormatConditions(1).Borders
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
xlWsh.UsedRange.FormatConditions(1).Interior.ColorIndex = 36

xlWsh.Range(“A1:AA1″).Font.Bold = True
xlWsh.UsedRange.Font.Size = 8.5
xlWsh.UsedRange.Columns.AutoFit

End With

End If
Next xlWsh
‘________________________________________________________

Call Color_Cells2

xlWbk.Close SaveChanges:=True
xlApp.Quit
Set xlWsh = Nothing
Set xlWbk = Nothing
Set xlApp = Nothing

End Sub

Private Sub Color_Cells2()

Dim xlApp As Object
Dim xlWbk As Object
Dim xlWsh As Object

Set xlApp = CreateObject(Class:=”Excel.Application”)
Set xlWbk = xlApp.Workbooks.Open(FileName:=strReportName)

‘_______________________________________________________________

For Each xlWsh In xlWbk.Worksheets

If xlWsh.Name = “aud_JO_MissingFields” Then

With Intersect(xlWsh.UsedRange, xlWsh.Columns(14)).FormatConditions
.Add Type:=xlExpression, Formula1:=”=$O1=””Open”””
.Item(1).Interior.ColorIndex = 36
End With

End If
Next xlWsh

End Sub
[/codebox]
________________________________

The error happening at
With Intersect(xlWsh.UsedRange, xlWsh.Columns(14)).FormatConditions
line.

Thanks for looking into this.

P.S. My actual columns is #14 and ‘Open’ value in column O.

Reply | Quote

Sorry, I keep forgetting that you’re not doing this in Excel itself. Change the lines

With Intersect(xlWsh.UsedRange, xlWsh.Columns(14)).FormatConditions
.Add Type:=xlExpression, Formula1:=”=$O1=””Open”””
.Item(1).Interior.ColorIndex = 36
End With

to

With xlApp.Intersect(xlWsh.UsedRange, xlWsh.Columns(14)).FormatConditions
.Add Type:=2, Formula1:=”=$O1=””Open”””
.Item(1).Interior.ColorIndex = 36
End With

Reply | Quote

I know the code is working, however when I am having it outside in another Private Sub – it opens another instance of Excel and works fine
except another instance of Excel is no good.

When I am inserting it into an old code – it gets ignored and no changes are happening.

I am trying a whole day and not able to find correct place for this ‘skeleton’…

Reply | Quote

Could you try to explain more clearly what you want to accomplish? Please don’t post your code again at this moment, just tell me what your goal is.

Reply | Quote

I have 5 Worksheets where only one “JO_MissingFields” has to have this done to it (but ONLY to one column #14)

With xlApp.Intersect(xlWsh.UsedRange, xlWsh.Columns(14)).FormatConditions .Add Type:=2, Formula1:=”=$O1=””Open””” .Item(1).Interior.ColorIndex = 36
End With
________________________________________________
– the rest of columns should stay colored as they were when first code ran.

P.S we have to remember that previously “JO_MissingFields” had other code running over it when it colored all blank cells.
Now we do not need those to be changed – we need only those in column 14 colored where $O1=””Open””.

It works fine when I ran it alone. When I am running it ‘within the code’ – it gets ignored.

Thanks and I can remove all codes, sorry.

Reply | Quote

Try the code in the attached text file.

Reply | Quote

Hans,

I can not believe my own luck to have you as my guide!

THANK YOU SO MUCH – you saved my face again!
And as I said I wish I could repay somehow…some day.

Reply | Quote