conditional format Excel from Access?

[voskouee]

Is there a way to color rows of Excel sheet from Access?

i did a search and on Excel forums but nothing.

Anyone did somehting like that?

for example is Column B has this text then color the whole row?

any ideas?

i found the code from cheating excel code but how do i condition so i dont declare rows..?

ActiveSheet.Rows("8").Interior.ColorIndex = 45

in these case rows will be those that fill the criteria..

thanks in advance

 

[The_Doc_Man]

The only way to do this conditionally from Access is to visit each cell and test whether it meets the criteria. If so, then you can do

ActiveSheet.Rows(X).Cells(Y).Interior.ColorIndex=..

ActiveSheet has two collections of interest. Rows and Columns. When you have Rows, the columns are Cells. When you have Columns, the rows are Cells.

That is, ActiveSheet.Row(2).Cell(3) is the same as ActiveSheet.Column(3).Cell(2) - just a matter of flipping X and Y.


I believe that some of the newer versions of Excel have conditional formating, but for that you would have to consult the help files.

 

[voskouee]

So you are telling me i have to search every single cell for the text i want? how do i test a range? how do i know where it ends?

can u give me an example of the IF statement how it will be?

thanks in advance..

 

[Bat17]

this will work from within XL but you will need to hack it a bit to work in access

Dim c As Object
Dim firstAddress As Variant
With Worksheets(1).Range("e4:E21")
    Set c = .Find(What:="d")
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            With c.EntireRow
                .Font.ColorIndex = 3
                With .Interior
                    .ColorIndex = 6
                    .Pattern = xlSolid
                End With
            End With
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

 

[voskouee]

i will give it a try my friend.

Thank you so much for your help

 

[Bat17]

might be easiest to just make it a sub and call it.

Sub ConditionRow(xlRange As Excel.Range, strFind As String)
Dim c As Object
Dim firstAddress As Variant
With xlRange
    Set c = .Find(What:=strFind)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            With c.EntireRow
                .Font.ColorIndex = 3
                With .Interior
                    .ColorIndex = 6
                    .Pattern = xlSolid
                End With
            End With
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With
End Sub

call it like

ConditionRow xlSheet.Range("d4:d21"), "C"

HTH

Peter

 

[Kenln]

Why can't you just set a conditional format on a row or column

Here is a VBA snippet from Access.

    xlApp.Rows("10:10").Select
    xlApp.Selection.FormatConditions.Delete
    xlApp.Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="0"
    xlApp.Selection.FormatConditions(1).Interior.ColorIndex = 3

Anyvalue less than zero will turn the entire cell red

Using

    xlApp.Rows("14:14").Select
    With xlApp.Selection.Interior
        .ColorIndex = 4
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With

The entire row will be green

 

[Bat17]

for example is Column B has this text then color the whole row?

will either of these methods achieve the original requirement?

Peter

 

[Kenln]

In the first example, if a value in the cell is less than zero then that cell will turn red. Not the entire row. It should be a simple matter selecting a column over a row.

If one is looking for any cell in a given column to turn a row a specific color then I would defer to your examples.

Mine is a simple approach and while working over a range it still afects each cell individually, i.e. the value in that cell affects the color of that cell.

 

[voskouee]

BAT 17 solutions works just fine. now its a matter of going through the whole workbook, finding all the sheets and validating the criteria.

SHould i do an array? group them together?

 

[voskouee]

Any ideas?

Dim c As Object
Dim firstAddress As Variant
Dim w As Excel.Worksheets

For w = Worksheets(1) To Worksheets(24)
With w.Range("d:d")
Set c = .Find(What:="11200000")
If Not c Is Nothing Then
firstAddress = c.Address
Do
With c.EntireRow
.Font.ColorIndex = 0
With .Interior
.ColorIndex = 46
.Pattern = xlSolid
End With
End With
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
Next
End With

 

[Bat17]

Dim c As Object
Dim firstAddress As Variant
Dim w As Object
For Each w In xlApp.Worksheets
    With w.Range("d:d")
        Set c = .Find(What:="11200000")
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                With c.EntireRow
                    .Font.ColorIndex = 0
                    With .Interior
                        .ColorIndex = 46
                        .Pattern = xlSolid
                    End With
                End With
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With
Next

You had the last 'End With' and 'Next' in the wrong order. If you indent your code it makes this sort of error easier to spot.

Peter