conditional formating if Teddy I

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

[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]

XLD,

it is still giving me a compile error

don't worry now it worked

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

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]

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

bump

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

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.

Sorry geekgirlau, I took too long to reply without refreshing :doh:

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

He he he... :devil2: stop playing with my name and it'll work fine

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

[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]

Quote:

---

Originally Posted by geekgirlau

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

---

Yes :) I saw into the future of your post and decided to write the same thing ;) (since your 15 hours 'ahead' of me)

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?

Quote:

---

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

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.

[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]

Charlize,

I feel it we are getting closer. Thank you for all the help so far. This is the code I have from yours:

[VBA]Sub HighlightTeddyProducts()
Dim cell As Range
Dim lookup As Range
Dim firstaddress As String
For Each cell In Worksheets(1).Range("B2", Range("B2").End(xlDown).Offset(-1, 0))
With WB(2).Worksheets(2).Range("C1:C9")
'Lookup number of description
Set lookup = .Find(cell, LookIn:=xlValues)
'In WB(2).Worksheets(2)
'looking for value of column I
If Not lookup Is Nothing Then
firstaddress = lookup.Address
Do
'if value found check for offset value
If lookup.Offset(, 8) = "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
WB(2).Close

End Sub[/VBA]

It isn't giving me an error but it doesn't highlight any of the products that it is supposed to highlight.

Quote:

---

Originally Posted by Djblois

Charlize,

I feel it we are getting closer. Thank you for all the help so far. This is the code I have from yours:

[vba]Sub HighlightTeddyProducts()
Dim cell As Range
Dim lookup As Range
Dim firstaddress As String
'or dim as workbooks ?
Dim CWB as workbook
set CWB = ThisWorkbook
For Each cell In CWB.Worksheets(1).Range("B2", Range("B2").End(xlDown).Offset(-1, 0))
Dim WB as workbook
Set WB = workbooks.open("your workbook with data to look for.xls")
With WB.Worksheets(2).Range("C1:C9")
'Lookup number of description
Set lookup = .Find(cell, LookIn:=xlValues)
'In WB(2).Worksheets(2)
'looking for value of column I
If Not lookup Is Nothing Then
firstaddress = lookup.Address
Do
'if value found check for offset value
If lookup.Offset(, 8) = "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
WB.Close

End Sub[/vba]

It isn't giving me an error but it doesn't highlight any of the products that it is supposed to highlight.

---