PDA

View Full Version : 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

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

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

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:
.FormatConditions.Add(Type:=xlExpression, Formula1:="=VLOOKUP(RC[1],'[BusinessReportingReference.xls] _
Products'!R1C3:R9C12,9,FALSE)=""Teddy I"")")

Djblois
03-12-2007, 09:13 AM
Glen,

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

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

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.

malik641
03-12-2007, 10:34 PM
Sorry geekgirlau, I took too long to reply without refreshing :doh:

geekgirlau
03-12-2007, 10:58 PM
Quite okay Joseph - I'm sure I was channelling you at the time anyway :wizard:

Aussiebear
03-13-2007, 01:05 AM
He he he... :devil2: 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
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

malik641
03-13-2007, 06:09 AM
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)

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
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.
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
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
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

Djblois
03-13-2007, 08:30 AM
Charlize,

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

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

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

Charlize
03-13-2007, 01:06 PM
Charlize,

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

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

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