?
Solved

Conditional formatting in Excel from Access

Posted on 2014-09-23
9
Medium Priority
?
160 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 85
ID: 40339701
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
ID: 40339707
Still nothing.
0
 
LVL 12

Expert Comment

by:danishani
ID: 40340000
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:Megin
ID: 40340295
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
 
LVL 12

Expert Comment

by:danishani
ID: 40340415
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
ID: 40340497
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 2000 total points
ID: 40340549
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
ID: 40342227
IT WORKED!

Thank you!
0
 
LVL 12

Expert Comment

by:danishani
ID: 40342238
Perfect thanks! :)
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Suggested Courses

801 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