Solved

Conditional formatting in Excel from Access

Posted on 2014-09-23
9
140 Views
Last Modified: 2014-09-24
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.
0
Comment
Question by:Megin
  • 4
  • 4
9 Comments
 
LVL 84
Comment Utility
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
 

Author Comment

by:Megin
Comment Utility
Still nothing.
0
 
LVL 12

Expert Comment

by:danishani
Comment Utility
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
 

Author Comment

by:Megin
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 12

Expert Comment

by:danishani
Comment Utility
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
 

Author Comment

by:Megin
Comment Utility
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
 
LVL 12

Accepted Solution

by:
danishani earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:Megin
Comment Utility
IT WORKED!

Thank you!
0
 
LVL 12

Expert Comment

by:danishani
Comment Utility
Perfect thanks! :)
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now