VBA conditional format Including Vlookup

J

jonofisher

Board Regular
Joined
May 25, 2005
Messages
51
Hello

I would like to conditional format some cells. Since there are more than 3 cases, i understand i need to use VBA. It gets tricky because I want to incorporate a vlookup in there somehow. the code runs but doesn't seem to work.

any help would be really, really appreciated. thanks heaps

The code i have written so far is:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim Cell As Range
Dim Rng1 As Range
     
    'range of the cells that i want to conditioanl format
    Set Rng1 = Range("b3:au55")
    
    If Rng1 Is Nothing Then
        Set Rng1 = Range(Target.Address)
        Else
        Set Rng1 = Union(Range(Target.Address), Rng1)
    
    For Each Cell In Rng1
    
    
    On Error Resume Next
    'i want to lookup the values in the conditional format range in a list in 
    'f57:t731 and then format based on the outcomes of the 15th column
Set Lookup = Application.WorksheetFunction.VLookup(ActiveCell, "$f$57:$t$731", "15", "FALSE")

'trying to format based on lookup result here:        
Select Case Lookup.Value
            Case ""
                ActiveCell.Interior.ColorIndex = xlNone
                AtivellCell.Font.Bold = False
            Case "Asset Services"
                ActiveCell.Interior.ColorIndex = 3
                Cell.Font.Bold = False
            Case "Business Services"
                ActiveCell.Interior.ColorIndex = 4
                Cell.Font.Bold = False
            Case "Business Strategy"
                Cell.Interior.ColorIndex = 5
                Cell.Font.Bold = False
            Case "Operations"
                Cell.Interior.ColorIndex = 6
                Cell.Font.Bold = False
            Case "Corporate Legal"
                Cell.Interior.ColorIndex = 7
                Cell.Font.Bold = False
            Case "HR"
                Cell.Interior.ColorIndex = 8
                Cell.Font.Bold = False
            Case "Program Management"
                Cell.Interior.ColorIndex = 9
                Cell.Font.Bold = False
            Case "Regulation"
                Cell.Interior.ColorIndex = 10
                Cell.Font.Bold = False
            Case "Asset Owner Interface"
                Cell.Interior.ColorIndex = 11
                Cell.Font.Bold = False
            Case "General Management"
                Cell.Interior.ColorIndex = 12
                Cell.Font.Bold = False
             Case "Internal Audit"
                Cell.Interior.ColorIndex = 13
                Cell.Font.Bold = False
            Case "Corporate Finance"
                Cell.Interior.ColorIndex = 14
                Cell.Font.Bold = False
            Case "Aam Finance"
                Cell.Interior.ColorIndex = 15
                Cell.Font.Bold = False
            Case "Alinta Corporate"
                Cell.Interior.ColorIndex = 16
                Cell.Font.Bold = False
            Case "APS"
                Cell.Interior.ColorIndex = 17
                Cell.Font.Bold = False
            Case "AIH"
                Cell.Interior.ColorIndex = 18
                Cell.Font.Bold = False
            
            Case Else
                Cell.Interior.ColorIndex = xlNone
                Cell.Font.Bold = False
        End Select
    Next
 
End If


End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Hi there,

There was some syntactical errors in your code. Without having anything to test with, you could try this ...


<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> Cell <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">If</SPAN> Intersect(Target, Range("B3:AU55")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Cell <SPAN style="color:#00007F">In</SPAN> Union(Target, Range("B3:AU55"))
<SPAN style="color:#007F00">'        On Error Resume Next</SPAN>
        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Application.WorksheetFunction.VLookup(Target, Me.Range("F57:T731"), 15, 0)
        <SPAN style="color:#00007F">Case</SPAN> ""
            Cell.Interior.ColorIndex = xlNone
            Cell.Font.Bold = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "Asset Services"
            Cell.Interior.ColorIndex = 3
            Cell.Font.Bold = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "Business Services"
            Cell.Interior.ColorIndex = 4
            Cell.Font.Bold = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "Business Strategy"
            Cell.Interior.ColorIndex = 5
            Cell.Font.Bold = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "Operations"
            Cell.Interior.ColorIndex = 6
            Cell.Font.Bold = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "Corporate Legal"
            Cell.Interior.ColorIndex = 7
            Cell.Font.Bold = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "HR"
            Cell.Interior.ColorIndex = 8
            Cell.Font.Bold = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "Program Management"
            Cell.Interior.ColorIndex = 9
            Cell.Font.Bold = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "Regulation"
            Cell.Interior.ColorIndex = 10
            Cell.Font.Bold = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "Asset Owner Interface"
            Cell.Interior.ColorIndex = 11
            Cell.Font.Bold = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "General Management"
            Cell.Interior.ColorIndex = 12
            Cell.Font.Bold = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "Internal Audit"
            Cell.Interior.ColorIndex = 13
            Cell.Font.Bold = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "Corporate Finance"
            Cell.Interior.ColorIndex = 14
            Cell.Font.Bold = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "Aam Finance"
            Cell.Interior.ColorIndex = 15
            Cell.Font.Bold = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "Alinta Corporate"
            Cell.Interior.ColorIndex = 16
            Cell.Font.Bold = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "APS"
            Cell.Interior.ColorIndex = 17
            Cell.Font.Bold = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "AIH"
            Cell.Interior.ColorIndex = 18
            Cell.Font.Bold = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN>
            Cell.Interior.ColorIndex = xlNone
            Cell.Font.Bold = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
    <SPAN style="color:#00007F">Next</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
Hi Fyrfighter

Thanks for the help. Unfortunately i still get an "unable to get the vlookup property of the worksheet function class" error. what does this mean?

thanks gain

jfish
 
Upvote 0
Hello, there, I had at first some trouble understanding what you were trying to achieve. Then eventually, I figure it out. You are trying to color code you account numbering by main group.

Try this slighthy modify version of your code. Put it as previously on the worksheet where you want your color to appear.

I put the line on error resume next as to have the code going [not good practise].

I put the coloring in a separate sub.

It works fine on my computer thought I only try with a few data, I didn't bother trying out ot the 731 row. You will have to do that.

I hope this works for you. Let me know how it goes .


Regards

Francoise

Code: 
Private Sub Worksheet_change(ByVal target As Range)
'a single cell
If target.Count = 1 Then
'in range b3:au55 has changed
If Not Intersect(target, ActiveSheet.Range("b3:au55")) Is Nothing Then
'i want to lookup the values in the conditional format range in a list in
'f57:t731 and then format based on the outcomes of the 15th column
'if cell is empty give neutral color
If VarType(target) = vbEmpty Then
target.Interior.ColorIndex = xlNone
target.Font.Bold = False
Exit Sub
End If
'if error will execute next line of code
On Error Resume Next
Dim therange As Range
Set therange = ActiveSheet.Range("f57:t731")
lookup = WorksheetFunction.vlookup(target, therange, 15, False)
'if error as occurred then neutral color
If Err.Number > 0 Then
target.Interior.ColorIndex = xlNone
target.Font.Bold = False
Exit Sub
End If
'if no error give color accordingly
Select Case lookup
Case "Asset Services"
change target, 3
Case "Business Services"
change target, 4
Case "Business Strategy"
change target, 5
Case "Operations"
change target, 6
Case "Corporate Legal"
change target, 7
Case "HR"
change target, 8
Case "Program Management"
change target, 9
Case "Regulation"
change target, 10
Case "Asset Owner Interface"
change target, 11
Case "General Management"
change target, 12
Case "Internal Audit"
change target, 13
Case "Corporate Finance"
change target, 14
Case "Aam Finance"
change target, 15
Case "Alinta Corporate"
change target, 16
Case "APS"
change target, 17
Case "AIH"
change target, 18
Case Else
target.Interior.ColorIndex = xlNone
target.Font.Bold = False
End Select
End If
End If
Set therange = Nothing
End Sub

Sub change(target As Range, colori As Byte)
With target
.Interior.ColorIndex = colori
.Font.Bold = False
End With
End Sub[quote][/quote]
 
Upvote 0
Just realise after posting that it only colours the current cell. Will fix that for you sometimes tomorrow.

Sorry for be too prompt in my answer.


May be after all you only want to color in the changing cell, not sure, please explain?????

Regards
Francoise
 
Upvote 0
That's interesting Francoise, I like that approach, saves much space. You can shorten it a little more. I've made some revisions, I hope you don't mind...


<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> LookupVal
    <SPAN style="color:#00007F">If</SPAN> Target.Count <> 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Me.Range("B3:AU55")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
        LookupVal = WorksheetFunction.VLookup(Target, Range("F57:T731"), 15, <SPAN style="color:#00007F">False</SPAN>)
        <SPAN style="color:#00007F">If</SPAN> Err.Number > 0 <SPAN style="color:#00007F">Then</SPAN> FormatCell Target, xlNone, False: <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> LookupVal
        <SPAN style="color:#00007F">Case</SPAN> "Asset Services": FormatCell Target, 3, <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "Business Services": FormatCell Target, 4, <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "Business Strategy": FormatCell Target, 5, <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "Operations": FormatCell Target, 6, <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "Corporate Legal": FormatCell Target, 7, <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "HR": FormatCell Target, 8, <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "Program Management": FormatCell Target, 9, <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "Regulation": FormatCell Target, 10, <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "Asset Owner Interface": FormatCell Target, 11, <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "General Management": FormatCell Target, 12, <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "Internal Audit": FormatCell Target, 13, <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "Corporate Finance": FormatCell Target, 14, <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "Aam Finance": FormatCell Target, 15, <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "Alinta Corporate": FormatCell Target, 16, <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "APS": FormatCell Target, 17, <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> "AIH": FormatCell Target, 18, <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> Else: FormatCell Target, xlNone, <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> FormatCell(rngRef <SPAN style="color:#00007F">As</SPAN> Range, iColor <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, iBold <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)
    rngRef.Interior.ColorIndex = iColor
    rngRef.Font.Bold = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


And btw jonofisher, VBA Vlookup will error if no match is found.
 
Upvote 0
Don't mind at all as code can always be improved and it is always good to have different opinion, as people see things differently and programming is like people unique to the hand of the programmer.

Thank you

Regards

Francoise
 
Upvote 0
Wow thanks for all that help. Yep, the code is now working very nicely indeed (and much shorter / quicker too). This has been really useful because i need to do this kind of thing quite often and have never been able to get it right...

Muchos gracias
 
Upvote 0
You must log in or register to reply here.

Similar threads

imback2nite
  • Solved
Worksheet Change on multiple sheets
2 3
Replies
21
Views
1K
My Aswer Is This
My Aswer Is This
L
  • Question
Conditional Formatting vba + Reverting the Conditional Formatting if no data
Replies
1
Views
142
leila
L
Chris_010101
  • Solved
VBA Clear Conditional Format
Replies
7
Views
687
Chris_010101
Chris_010101
imback2nite
  • Question
Is there a way to have code work on selected sheets?
Replies
3
Views
436
Kamolga
K
D
  • Question
VBA conditional format of a shape ....not working
Replies
6
Views
414
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,215,003
Messages
6,122,655
Members
449,091
Latest member
peppernaut

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top