Conditional formatting in Excel from Access

I need to apply some conditional formatting to a report that I am exporting from an Access database. It should be easy, but I can't figure out the exact phrasing.  This is what I have now:

With oSheet.Range("A1:" & Chr(iNumCols + 64) & rs.RecordCount + 1)
If Range("A:A") = "BA-Lots of Stuff" Then
    .Interior.Pattern = xlSolid
    .Interior.PatternColorIndex = xlAutomatic
    .Interior.ThemeColor = xlThemeColorDark1
End If

Open in new window


I also tried having it as
If "BA-Lots of Stuff" = true then ....     but that didn't work either.

Actually, nothing happens when I tried to run either version.
MeginAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Have you tried using:

If Range("A:A").Value = "BA-Lots of Stuff" Then

or possibly:

If Range("A:A").Value2 = "BA-Lots of Stuff" Then
0
MeginAuthor Commented:
Still nothing.
0
danishaniCommented:
Are you able to post the whole code to loop through the range?
In order to change the Cell value, you need to loop through all of them in order to capture the value you are looking for and then change the formatting.

See for an example below reference from Microsoft:
Looping Through a Range of Cells

Hope this helps,
Daniel
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MeginAuthor Commented:
It totally makes sense that I would have to loop through a range of cells here. I hadn't thought of that!

I am still stuck for how to introduce that into my code. What I have for the Excel part of the code is below. I am assuming that I will need to put this code for conditional formatting at the end of this. But how to I use those examples? The "For Each...Next" loop seems to be appropriate, but will I need to set up new declarations? I see that the example says "For Each c In..."

Would it then be followed by "If c.value = "thing I am looking for here" Then .interior.pattern = solid......"?


Dim oApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRange As Excel.Range

Set oBook = oApp.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
oApp.Visible = True
oApp.UserControl = True

'Add the field names in row 1
Dim i As Integer
Dim iNumCols As Integer
Dim TheTO As String
Dim TheSTOname As String
Dim TheStaffName As String
Dim theActDesc As String


'ok We want each task order to be on a row by itself in column A
'Headers
oSheet.Cells(1, 1).Value = "Task Order"

oSheet.Cells(1, 2).Value = "Sub Tasks"

oSheet.Cells(1, 3).Value = "Staff"

oSheet.Cells(1, 4).Value = "Details"
iNumCols = 4

theRow = 3
TheTO = rs!to
oSheet.Cells(2, 1).Value = TheTO
TheSTOname = rs!STO
oSheet.Cells(3, 1).Value = TheSTOname
TheStaffName = rs!TeamName
oSheet.Cells(3, 2).Value = TheStaffName
theActDesc = rs!ActDesc
oSheet.Cells(3, 3).Value = theActDesc

rs.MoveNext

Do Until rs.EOF

    If rs!to = TheTO Then
        If rs!STO = TheSTOname Then
            If TheStaffName = rs!TeamName Then
                If theActDesc = rs!ActDesc Then
                    rs.MoveNext
                Else
                    theRow = theRow + 1
                    oSheet.Cells(theRow, 3).Value = rs!ActDesc
                    rs.MoveNext
                End If
            Else
                'theRow = theRow + 1
                oSheet.Cells(theRow, 2).Value = rs!TeamName
                TheStaffName = rs!TeamName
                rs.MoveNext
            End If
        Else
            theRow = theRow + 1
            oSheet.Cells(theRow, 1).Value = rs!STO
            TheSTOname = rs!STO
            TheStaffName = ""
            
            rs.MoveNext
        End If
    Else
        theRow = theRow + 1
        oSheet.Cells(theRow, 1).Value = rs!to
        TheTO = rs!to
        rs.MoveNext
    End If
skip:
Loop

iNumCols = IIf(w > 0, w, iNumCols)
'Format the header row as bold and autofit the columns
With oSheet.Range("a1").Resize(1, iNumCols)
.Font.Bold = True
.EntireColumn.AutoFit
.Columns("A:A").ColumnWidth = 40
.Columns("B:B").ColumnWidth = 22
.Columns("C:C").ColumnWidth = 73.44
End With
With oSheet.Range("A1:" & Chr(iNumCols + 64) & rs.RecordCount + 1)

Open in new window

0
danishaniCommented:
I did not test it, but just applying the logic, try this:
For Each c In oSheet.Range("A1:" & Chr(iNumCols + 64) & rs.RecordCount + 1).Cells
  If c.Value = "BA-Lots of Stuff" Then
    c.Interior.Pattern = xlSolid
    c.Interior.PatternColorIndex = xlAutomatic
    c.Interior.ThemeColor = xlThemeColorDark1
  End If
Next

Open in new window


Hope this helps,
Daniel
0
MeginAuthor Commented:
There is still nothing happening. I went ahead and wrote a dim statement declaring c as an object. Then I added the code, but nothing happens when I try to run it.

If it matters, none of the conditions I will be using will ever repeat. I just want to tell excel 'if the cell contains the word "Example" then shade it in, add borders, etc.' I guess it could just as easily be 'find the cell that contains the word "example"'


Dim i As Integer
Dim iNumCols As Integer
Dim TheTO As String
Dim TheSTOname As String
Dim TheStaffName As String
Dim theActDesc As String
Dim c As Object


'ok We want each task order to be on a row by itself in column A
'Headers
oSheet.Cells(1, 1).Value = "Task Order"

oSheet.Cells(1, 2).Value = "Sub Tasks"

oSheet.Cells(1, 3).Value = "Staff"

oSheet.Cells(1, 4).Value = "Details"
iNumCols = 4

theRow = 3
TheTO = rs!to
oSheet.Cells(2, 1).Value = TheTO
TheSTOname = rs!STO
oSheet.Cells(3, 1).Value = TheSTOname
TheStaffName = rs!TeamName
oSheet.Cells(3, 2).Value = TheStaffName
theActDesc = rs!ActDesc
oSheet.Cells(3, 3).Value = theActDesc

rs.MoveNext

Do Until rs.EOF

    If rs!to = TheTO Then
        If rs!STO = TheSTOname Then
            If TheStaffName = rs!TeamName Then
                If theActDesc = rs!ActDesc Then
                    rs.MoveNext
                Else
                    theRow = theRow + 1
                    oSheet.Cells(theRow, 3).Value = rs!ActDesc
                    rs.MoveNext
                End If
            Else
                'theRow = theRow + 1
                oSheet.Cells(theRow, 2).Value = rs!TeamName
                TheStaffName = rs!TeamName
                rs.MoveNext
            End If
        Else
            theRow = theRow + 1
            oSheet.Cells(theRow, 1).Value = rs!STO
            TheSTOname = rs!STO
            TheStaffName = ""
            
            rs.MoveNext
        End If
    Else
        theRow = theRow + 1
        oSheet.Cells(theRow, 1).Value = rs!to
        TheTO = rs!to
        rs.MoveNext
    End If
skip:
Loop

iNumCols = IIf(w > 0, w, iNumCols)
'Format the header row as bold and autofit the columns
With oSheet.Range("a1").Resize(1, iNumCols)
.Font.Bold = True
.EntireColumn.AutoFit
.Columns("A:A").ColumnWidth = 40
.Columns("B:B").ColumnWidth = 22
.Columns("C:C").ColumnWidth = 73.44
End With

oSheet.Range("A:A").NumberFormat = "dd-mmm-yy "

'oSheet.Range(Cells(1, 1), Cells(iNumCols, rs.RecordCount + 1)).Copy


With oSheet.Range("A1:" & Chr(iNumCols + 64) & rs.RecordCount + 1)
For Each c In oSheet.Range("A1:" & Chr(iNumCols + 64) & rs.RecordCount + 1).Cells
If c.Value = "BA-Lots of Stuff" Then
    c.Interior.Pattern = xlSolid
    c.Interior.PatternColorIndex = xlAutomatic
    c.Interior.ThemeColor = xlThemeColorDark1
  End If
Next

Open in new window

0
danishaniCommented:
For some reason the properties you are using are not supported for the cell object, try using something like this, for example:
For Each c In oSheet.Range("A1:" & Chr(iNumCols + 64) & rs.RecordCount + 1).Cells
    If c.Value = "BA-Lots of Stuff" Then
        With c
           .Font.Bold = True
           .Font.Color = vbRed
     End With
  End If
Next c

Open in new window


Hope this helps,
Daniel
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MeginAuthor Commented:
IT WORKED!

Thank you!
0
danishaniCommented:
Perfect thanks! :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.