Highlighting rows using VBA in excel 2003!

[Slimline]

Hello,

I am using Excel 2003 and conditional formatting (CF) only has a limit of 3.

So I wanted to know if I can add a VBA code to add more CF to highlight rows based on a criteria/text in just one cell.
E.g:
Collumn D= Decision
Collumn E= Request date
Collumn F= Expiry date

When 'n/a' is selected in D2 from the dropdown list, I want the whole row from A2 to G2 to be highlighted in BLUE.
When 'Accept' chosen = GREEN colour from A2 to D2 only.
When 'Reject' chosen = GREY colour from A2 to D2 only.

Also I want it to override the RED colour in E2-F2, when a 'Decision' is selected. This is because I have used (well gave it a go) CF to highlight when expiry is reached.
But when D2 is deleted, the colour disappears & red colour comes back (as that row is still expired).

There seems to be some descrepancy in the formula too as it is even highlighting blank cells in red. Any fixation on that would be great.

I have uploaded the file.

Many Thanks,

Serena

 

[bobhc]

Good day Serena

These links should get you going in the right direction


http://chandoo.org/wp/2008/10/14/more-than-3-conditional-formats-in-excel/

http://www.599cd.com/tips/excel/extend-conditional-formatting/ complete with video

 

[Slimline]

Hi,
Thanks for the above, but the first link was beyond my understanding, so I used the code in the second link.

I tried using the code below, however it only highlights one cell. How can I extend it to highlight a row as I have stated above.

One more problem is that the code doesn't work if I change my range to ($d2) as I want to apply it to the whole of column D.


Private Sub Worksheet_Change(ByVal Target As Range)
Set I = Intersect(Target, Range("d2:d10"))
If Not I Is Nothing Then

Select Case Target
Case Is = "n/a": NewColor = 3 ' red

End Select
Target.Interior.ColorIndex = NewColor
End If
End Sub

 

[NARAYANK991]

Hi Serena ,

Can you wait for a day ? I can upload the revised file either today or tomorrow.

Narayan

 

[Slimline]

Hello again Narayan,

Yes, thats completely fine. I look forward to it.

Cheers!
Serena

 

[SirJB7]

Hi, Slimline!

Give a look at the uploaded file. The VBA code is this:

Option Explicit
 
Sub ConditionalFormatForPaleozoicExcelVersions()
    ' constants
    Const ksWS = "Sheet1"
    Const ksData = "DataTable"
    Const ksNA = "N/A"
    Const ksAccept = "Accept"
    Const ksReject = "Reject"
    Const klBlue = &HFF8080
    Const klGray = &HC0C0C0
    ' declarations
    Dim rng As Range
    Dim I As Long, J As Integer, A As String
    ' start
    Set rng = Worksheets(ksWS).Range(ksData)
    ' process
    With rng
        For I = 1 To .Rows.Count
            ' reset
            With .Rows(I).Cells.Interior
                .Pattern = xlNone
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
            ' paint
            A = .Cells(I, 4).Value
            Select Case A
                Case ksNA
                    .Rows(I).Cells.Interior.Color = klBlue
                Case ksAccept
                    Range(.Cells(I, 1), .Cells(I, 4)).Interior.Color = vbGreen
                Case ksReject
                    Range(.Cells(I, 1), .Cells(I, 4)).Interior.Color = klGray
                Case Else
                    If .Cells(I, 6).Value < Date Then
                        Range(.Cells(I, 5), .Cells(I, 6)).Interior.Color = vbRed
                    End If
            End Select
        Next I
    End With
    ' end
    Set rng = Nothing
    Beep
End Sub

Just advise if any issue.

Regards!

 

[Slimline]

Hi SirJB7,

I tried copying the code but it doesnt work. A 'compile error' message occurs and 'option explicit' in the code is highlighted. I tried deleting the previous code, but still doesnt work.

Thanks,
Serena

 

[NARAYANK991]

Hi Serena ,

I am sure SirJB7 ( whose real name is Pablo , by the way ) , will respond to your question ; however , I would suggest that since he has uploaded a working file , you download it and run the relevant procedure ( macro ) and see how it works.

His code posted here has the Option Explicit statement at the top ; when you copy his code and paste it in your file , you have to ensure that you do it so that the Option Explicit statement is right at the top of the code window ; suppose you already have a procedure or two in your code window , you cannot copy Pablo's code and paste it after your existing code segments , since then the Option Explicit statement will come in between two sections of code ; this is an error.

Ensure that when you paste Pablo's code , you do it before any other code that might already be present in your code window.

Secondly , Pablo's code will not run automatically ; you will have to run it. Enter all of your data , and then execute his macro to see the CF results.

Narayan

 

[Slimline]

Hi Narayan,

I just tried what you said, but when i run the macro (i.e. tools>maro>run....i hope that what you meant?), a 'run time error/438' occurs.
Also 'TintAndShade = 0' highlishts in yellow.
Then i tried creating a new duplicate of the sheet and copied the code, but still can't get it to work.

I have uploaded the new copy. Please take a look as I would really like to know where I'm going wrong.
Serena

 

[Slimline]

Hi Guys,

I know I have asked for a no.of CF to happen & hence the code is complex. However I'm really keen to learn the basics.
So can you please tell me what a simple CF code will ook like if I kust wanted to have:
Collumn D= Decision
When 'Accept' chosen = GREEN colour from A2 to D2 only. Otherwise no colour.
I have been looking around, but only managed to find a code that will highlight just one cell but not a specific length of row & when cell is blank- there is no colour.

Thanks for your input guys!
Serena.

 

[NARAYANK991]

Hi Serena ,

Check the file now.

Narayan

 

[bobhc]

Good day Slimeline

Please look at the attachment, I have done three CF's is this what you want?

 

[SirJB7]

Hi, Serena!
Check this updated file. I seems as if the properties of the Interior object have changed since 2003 version to 2007+, fact that I didn't remember.
I updated the code as this:

            With .Rows(I).Cells.Interior
                Select Case iVersion
                    Case Is <= ki2003
                        .ColorIndex = xlColorIndexNone
                    Case Is >= ki2007
                        .Pattern = xlNone
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                End Select
            End With

Hope it works, I think it will, since the ColorIndex property works too for 2007+. Regarding this the code would be as follows:

            With .Rows(I).Cells.Interior
                    .ColorIndex = xlColorIndexNone
                End Select
            End With

or:

            .Rows(I).Cells.Interior.ColorIndex = xlColorIndexNone

In the file you have the 1st version of these 3, just in case Murphy wants to appear, but if that 1st works then any of the other 2 will do the job either.
Just advise if any issue.
Regards!

 

[Slimline]

Hi SirJB7 (Pablo),

So the new file works . However it only works when I go to >Tools>Marcos>Run.

The row will only highlight when I 'run' the macro. Thats the case for every row and when I open it in Excel 2003 & 2010.

Getting close - so what can I do so the macro runs automatically when I open the file?


Hi Bobhc,

Many thanks for your input but I need the whole row to be highlighted.

-Serena

 

[SirJB7]

Hi, Slimline!

Macros in standard modules or if in workbook or worksheet class modules and not event driven, only works manually triggered, i.e., run from the macro menu Alt-F8 or assigned to an object event (click on command buttons, shapes, etc.).

For being automatically triggered add this code to the workbook class module to handle the open workbook event (updated file uploaded too):

Option Explicit
 
Private Sub Workbook_Open()
    ConditionalFormatForPaleozoicExcelVersions
End Sub

Regards!