VBA for shading and borders

jayphilips

New Member
Joined
Jul 24, 2009
Messages
14
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
 

jayphilips

New Member
Joined
Jul 24, 2009
Messages
14
I did have it working with the conditional format using:
Code:
 =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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Record a macro while doing it manually (you don't need to use INDIRECT). That will give you some code.
 

jayphilips

New Member
Joined
Jul 24, 2009
Messages
14
I tried recording a macro for it but the macro doesn't do the if over 10%

Here's the macro code:
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:
Code:
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:
Code:
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Jul 24, 2009
Messages
14
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:
Code:
=INDIRECT("E"&ROW())>10%
For the ones that were below 10% I used this formula so it still did the boxes:
Code:
=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.
 
Last edited:

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try this:

Code:
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

New Member
Joined
Jul 24, 2009
Messages
14
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
I tested my code before I posted it. Any row where the value in column E is greater than 10 is coloured blue.
 

Forum statistics

Threads
1,081,624
Messages
5,360,093
Members
400,570
Latest member
Ben Morgan 1985

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top