Replies

You people are awesome! I can’t thank you enough. THANKS A MILLION for your help!

I would love to – however I do not have an example – this is what is happening…

I took the formula from Crystal Report and I want to re-do it for Access Report.

What I am trying to do is following:

=IIf([Full/Part Time]=”F” And [Transfer]=0 And [New Hire]=0
*display Name with SUB A Joe, Doe – ERT
([LAST NAME] & “,” & [FIRST NAME] & “-” & ))

IIf([Transfer]=0 And [New Hire]=0
*display Name with FT or PT and SUB A like Joe, Doe (F) – ERT
[LAST NAME] & “,” & [FIRST NAME] & ” (” & [FULL/PART TIME] & “) ” & “- ” & )

Or IIf([New Hire]=0
*display Name with tilda in front ~ Joe, Doe – ERT to PRT
“~” & [LAST NAME] & “,” & [FIRST NAME] & “-” & [Prior SUB] & ” to ” & ,”+” & [LAST NAME] & “,” & [FIRST NAME] & ” (” & [FULL/PART TIME] & “) ” & “- ” & )

else
+” & [LAST NAME] & “,” & [FIRST NAME] & ” (” & [FULL/PART TIME] & “) ” & “- ” & )
*display Name like + Joe, Doe (F) – PRT
________________________________________________________

If this was not 100% accurate – below is Crystal’s formula:

if {TABLE1.FULL/PART TIME} = “F” and {TABLE1.Transfer} = 0 and {TABLE1.New Hire} = 0
then
{TABLE1.LAST NAME}& “,”& {TABLE1.FIRST NAME} & “-” & {TABLE1.SUB LOB}
else
if {TABLE1.Transfer} = 0 and {TABLE1.New Hire} = 0
then
{TABLE1.LAST NAME}& “,”& {TABLE1.FIRST NAME}& ” (” & {TABLE1.FULL/PART TIME} &”) “& “- ” & {TABLE1.SUB LOB}
else
if {TABLE1.New Hire} = 0
then
“~” & {TABLE1.LAST NAME}& “,”& {TABLE1.FIRST NAME} & “-” & {TABLE1.Prior SUB} & ” to ” & {TABLE1.SUB LOB}
else
“+” &{TABLE1.LAST NAME}& “,”& {TABLE1.FIRST NAME} & ” (” & {TABLE1.FULL/PART TIME} &”) “& “- ” & {TABLE1.SUB LOB}

*********THANKS SO MUCH!!!

I am using Access 2003.

Where do I enter in a query? Thanks

Francois!
It seems like it is all I need! I will have to take a beter look but I see numbers I need.

Thanks so much for your time and help and have a great day!

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.

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.

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’…

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.

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.

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.

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.

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…?

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

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 :-))

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