conditional formating if Teddy I

**Djblois** · 03-12-2007, 08:05 AM

I am using code to highlight a cell if the vlook returns a value of Teddy I. Here is the code I tested

[vba]With Range("B2", Range("B2").End(xlDown).Offset(-1, 0))
With .FormatConditions.Add Type:=xlExpression, _
Formula1:="=if(VLOOKUP(RC[1], _
'[BusinessReportingReference.xls]Products'!C1:C9,9,FALSE)=Teddy I)"
.Font.Bold = True
.Font.ColorIndex = 3
.Interior.ColorIndex = 8
End With
End With[/vba]

However it keeps giving me an error on the with .formatconditions line

Edited 13-Mar-07 by geekgirlau. Reason: insert line breaks

**Bob Phillips** · 03-12-2007, 08:18 AM

[vba]

With Range("B2", Range("B2").End(xlDown).Offset(-1, 0))
With .FormatConditions.Add(Type:=xlExpression, _
Formula1:="=VLOOKUP(RC[1],'[BusinessReportingReference.xls]Products'!R1C3:R9C12,9,FALSE)=""Teddy I""")
.Font.Bold = True
.Font.ColorIndex = 3
.Interior.ColorIndex = 8
End With
End With
[/vba]

**Djblois** · 03-12-2007, 08:33 AM

XLD,

it is still giving me a compile error

**Djblois** · 03-12-2007, 08:34 AM

don't worry now it worked

**Djblois** · 03-12-2007, 08:36 AM

I am no longer getting a compile error but I am getting an invalid procedure call or argument on the formatconditions line

**moa** · 03-12-2007, 08:50 AM

Youre missing a bracket by the looks:
[vba].FormatConditions.Add(Type:=xlExpression, Formula1:="=VLOOKUP(RC[1],'[BusinessReportingReference.xls] _
Products'!R1C3:R9C12,9,FALSE)=""Teddy I"")")[/vba]

**Djblois** · 03-12-2007, 09:13 AM

Glen,

your code is giving me a compile error. This is what I have:

[VBA]With Range("B2", Range("B2").End(xlDown).Offset(-1, 0))
With .FormatConditions.Add(Type:=xlExpression, _
Formula1:="=VLOOKUP(RC[1],'[BusinessReportingReference.xls]Products'!R1C3:R9C12,9,FALSE)=""Teddy I""")
.Font.Bold = True
.Font.ColorIndex = 3
.Interior.ColorIndex = 8
End With
End With[/VBA]

and it is giving me an invalid procedure call or argument on the formatconditions line

**Djblois** · 03-12-2007, 07:40 PM

bump

**geekgirlau** · 03-12-2007, 10:10 PM

As far as I know, you cannot have a reference to another sheet or workbook in conditional formatting.

**malik641** · 03-12-2007, 10:32 PM

Have you tried entering the conditional formatting without the code? I tried without the code, and it kept giving me errors in the formula about the R1C1 notation. After I adjusted that to reflect "A1" notation it gave me an error stating:

"You may not use references to other worksheets or workbooks for Conditional Formatting criteria."

Is the condition based on the current worksheet in the current workbook or is it elsewhere? Maybe this is your problem.

**geekgirlau** · 03-12-2007, 10:58 PM

Quite okay Joseph - I'm sure I was channelling you at the time anyway

**Aussiebear** · 03-13-2007, 01:05 AM

He he he...

stop playing with my name and it'll work fine

**Djblois** · 03-13-2007, 04:46 AM

I tried it and thought it worked. Maybe it was a fluke. Is there another way of doing it? Not through conditional formating, maybe?

**Charlize** · 03-13-2007, 05:36 AM

[VBA]Sub formatting_teddy()
Dim cell As Range
Dim lookup As Range
Dim firstaddress As String
'every cell in B2:BX - X
For Each cell In Worksheets(1).Range("B2", Range("B2").End(xlDown).Offset(-1, 0))
'This is the list with descriptions
With Worksheets(2).Range("C1:C9")
'Lookup number of description
Set lookup = .Find(cell, LookIn:=xlValues)
'In sheets(2) or for you the businessreports
'1 in column C - Teddy I in column D
'2 in column C - Another description in D
'...
'9
'If 1 we color the cell
If Not lookup Is Nothing And lookup = 1 Then
firstaddress = lookup.Address
Do
cell.Interior.ColorIndex = 8
Set lookup = .FindNext(lookup)
Loop While Not lookup Is Nothing And lookup.Address <> firstaddress
End If
End With
Next cell
End Sub[/VBA]

**Djblois** · 03-13-2007, 07:00 AM

Charlize,

I am sorry but I don't understand your code. How do I use it? I can't figure out where it attaches to the other spreadsheet to check if it finds Teddy I? Please help I am confused?

**Charlize** · 03-13-2007, 07:16 AM

Originally Posted by Djblois

Charlize,

I am sorry but I don't understand your code. How do I use it? I can't figure out where it attaches to the other spreadsheet to check if it finds Teddy I? Please help I am confused?

I thought that you needed to check for an offset value (needs to be added) of a value that you searched for. So you look for 1 in sheet2. If found, check on Teddy I (offset column) and color if true.
[vba]If Not lookup Is Nothing Then
firstaddress = lookup.Address
Do
If lookup.Offset(,9) = "Teddy I" then
cell.Interior.ColorIndex = 8
End If
Set lookup = .FindNext(lookup)
Loop While Not lookup Is Nothing And lookup.Address <> firstaddress
End If
[/vba]Charlize

**Djblois** · 03-13-2007, 07:36 AM

Charlize,

here is an example of what I am trying to do:

In the spreadsheet that I am testing:
345257
487585
120565
In the spreadsheet that I am looking up in
345257 Teddy I
487585
120565 Teddy I
This is what the original would look like:
345257
487585
120565

I tried to do it with a V-Look up and it didn't work.

**Charlize** · 03-13-2007, 07:52 AM

[vba]Sub formatting_teddy()
Dim cell As Range
Dim lookup As Range
Dim firstaddress As String
'every cell in B2:BX - X / X is not in the range to be formatted
For Each cell In Worksheets(1).Range("B2", Range("B2").End(xlDown).Offset(-1, 0))
'This is the list with descriptions
With Worksheets(2).Range("C1:C9")
'Lookup number of description
Set lookup = .Find(cell, LookIn:=xlValues)
'In sheets(2) or for you the businessreports
'looking for value of column b
If Not lookup Is Nothing Then
firstaddress = lookup.Address
Do
'if value found check for offset value
If lookup.Offset(, 1) = "Teddy I" Then
'if true then column b is bold
cell.Font.Bold = True
End If
'next search
Set lookup = .FindNext(lookup)
Loop While Not lookup Is Nothing And lookup.Address <> firstaddress
End If
End With
Next cell
End Sub[/vba]

Thread: conditional formating if Teddy I

Thread Tools

Display

conditional formating if Teddy I

Posting Permissions