help with coding in excel

[associates]

Hi,

How do i write a code in VB to define names for cells in excel?

The following is the code i use but doesn't work.

worksheets("Data").Names.Add Name:="myF4",
RefersToR1C1:="=Sheet1!R4C4"
worksheets("Data").FormatConditions.Add Type:=xlCellValue,
Operator:=xlGreater, Formula1:="50"
worksheets("Data").FormatConditions(1).Interior.ColorIndex = 6
worksheets("Data").FormatConditions.Add Type:=xlCellValue,
Operator:=xlLess, Formula1:="50"
worksheets("Data").FormatConditions(2).Interior.ColorIndex = 42

Thank you for your help.

 

[Bob Phillips]

CF applies to a range not a sheet

With Worksheets("Data")
.Names.Add Name:="myF4", RefersToR1C1:="=Sheet1!R4C4"
With .Range("A1")
.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater,
Formula1:="50"
.FormatConditions(1).Interior.ColorIndex = 6
.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess,
Formula1:="50"
.FormatConditions(2).Interior.ColorIndex = 42
End With
End With


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[associates]

Hi, Bob

Thank you for your reply.

I got no error messages anymore but very strange.

The following is the code you gave me and modified by me.
With Worksheets("Sheet3")
.Names.Add Name:="myF4", RefersToR1C1:="=Sheet3!R4C6"
.Names.Add Name:="myF5", RefersToR1C1:="=Sheet3!R5C4"
End With

With Worksheets("Data")
With .Range("F4")
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual
Formula1:="=myF4"
.FormatConditions(1).Interior.ColorIndex = 6
.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual
Formula1:="=myF4"
.FormatConditions(2).Interior.ColorIndex = 42
End With
With .Range("F5")
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual
Formula1:="=myF5"
.FormatConditions(1).Interior.ColorIndex = 6
.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual
Formula1:="=myF5"
.FormatConditions(2).Interior.ColorIndex = 42
End With
End With

Based on the code, the value of cell,F4, on worksheet "Data" should b
changed if it's not the same as myF4 on worksheet "Sheet3" but i
doesn't change the color. the same problem for cell F5.

I'm actually working between worksheet "Data" and "Sheet3".

Any ideas?

Thank you in advanc

 

[Bob Phillips]

That is because you have created names local to sheet3, so the CF in the
data sheet cannot see these names.

Try this

With ActiveWorkbook
.Names.Add Name:="myF4", RefersToR1C1:="=Sheet3!R4C6"
.Names.Add Name:="myF5", RefersToR1C1:="=Sheet3!R5C4"
End With

With Worksheets("Data")
With .Range("F4")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual,
Formula1:="=myF4"
.FormatConditions(1).Interior.ColorIndex = 6
.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual,
Formula1:="=myF4"
.FormatConditions(2).Interior.ColorIndex = 42
End With
With .Range("F5")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual,
Formula1:="=myF5"
.FormatConditions(1).Interior.ColorIndex = 6
.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual,
Formula1:="=myF5"
.FormatConditions(2).Interior.ColorIndex = 42
End With
End With

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)