VBA for shading and borders

[jayphilips]

Hi,

I am trying to write vba code that will highlight the row in the range if a field is over a certain percent. The column number won't change but the number of rows will. I'd also like the code to automatically work on all tabs of the workbook when a button is clicked.

Data Info:
Currently there are 4 tabs, but can have more/less
Columns used are A:O
Data for shading starts at A3 and should go to however many rows have data and ignore blank rows
Formula should be if data in column E is over 10.00% then the data in that row A:O should be shaded in the color off yellow & have black thin orders
If the data in column E is not over 10.00% then the data in that row should not be shaded but still have thin black borders.

Can anyone help with the vba code for this?

Excel version is 2003

Thanks in advance,
Jay

 

[Andrew Poulsom]

What's wrong with conditional formatting with the formula?

=$E3>0.1

 

[jayphilips]

I did have it working with the conditional format using:

 =INDIRECT("E" & Row()) > 10

The issue with this is that it doesn't automatically work on every tab added so which is why I was hoping to get VBA code to work.

 

[Andrew Poulsom]

Record a macro while doing it manually (you don't need to use INDIRECT). That will give you some code.

 

[jayphilips]

I tried recording a macro for it but the macro doesn't do the if over 10%

Here's the macro code:

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A3:O3").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 10092543
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Here's what I tried but doesn't thows an errors say Ros isn't defined:

Sub myAddColor()
'Code will shade the row in an off yellow color if the data in Column E is over 10.00%
    Range("A3:O65536").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
    Formula1:=INDIRECT("E" & Row()) > 10
    Selection.FormatConditions(1).Interior.ColorIndex = 5
    Range("A3").Select
End Sub

I also tried this:

Sub FormatRowColor()
Dim MyCell As Range, MyRange As Range
    Set MyRange = Range("A3:O30")
    For Each MyCell In MyRange
        'If MyCell.Value > Range("10").Value Then
        'If ActiveCell.EntireRow.Cells(1, 1).Value > 10 Then
        If Cells(Application.ActiveCell.Row, 5).Select > 10 Then
            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 10092543
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        End If
    Next MyCell
End Sub

Any ideas?

 

[Andrew Poulsom]

The Formula1 argument is a string, so it should be enclosed in quotes:

Formula1:="=INDIRECT(""E"" & ROW()) > 10"

I still don't know why you need INDIRECT.

 

[jayphilips]

I tried that but it still doesn't change the row color.

I was using Indirect because that's the way it worked when I tried just the Conditional Formatting option. It required the Indirect since I wanted all the columns in the row (A:O) if the data in column E was over 10%.

What is a better option to go?

NOTE:
For the Yellow coloring & boxes I used this formula in the Conditional Formatting:

=INDIRECT("E"&ROW())>10%

For the ones that were below 10% I used this formula so it still did the boxes:

=NOT(ISBLANK(A1))

It would be nice if I could just make those two conditions as vba since that's what I need it to do in the end.

 

[Andrew Poulsom]

Try this:

Sub myAddColor()
'Code will shade the row in an off yellow color if the data in Column E is over 10.00%
    With Range("A3:O65536")
        With .FormatConditions
            .Delete
            .Add Type:=xlExpression, Formula1:="=RC5>10"
        End With
        .FormatConditions(1).Interior.ColorIndex = 5
    End With
End Sub

 

[jayphilips]

I copied and pasted the code you posted and tried to run it. The mouse changed like it was doing something but the rows that should have changed colors didn't.

Oh, the other reason why I went away from the Conditional Formatting option was because ever day the rows will change & data will change so when you remove the data from one day and put te next day data the rows weren't updating with the new formatting unless you manually updated the conditional formatting.

 

[Andrew Poulsom]

I tested my code before I posted it. Any row where the value in column E is greater than 10 is coloured blue.