VBA Conditional Formatting

[Stimpy]

Hello, I have a bit of a problem. I want to change the color of a cell based on the value of another cell in another worksheet.
So basically, If in cell B8 I fill in 123 and that matches one of the batch numbers on sheet 2, B8 should turn green.

I have tried in 2 ways, but both seem to be lacking. Any insight?

The first one is


Private Sub Worksheet_Change(ByVal Target As Range)

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="='Batch Data'!$B$2"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

It works decent, but I can't seem to select a range numbers. Just the one (B2). If I change it to say B2:B10 it gives an error.

The other one is this


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rg As Range
Dim cond1 As FormatCondition
Set rg = Range("$B$8", Range("$B$8").End(xlDown))
Set cond1 = rg.FormatConditions.Add(xlCellValue, xlEqual, "=$A$10")
With cond1
.Interior.Color = vbGreen
.Font.Color = vbWhite
End With
End Sub

This one also sort of works, but I cant seem to get it to get data from the second sheet.
So, any idea's? Or is there a better way to do this?
I tried not using conditional formatting and just filling in the cells, but that didn't work out either.

Help? :)

[rlv]

This one also sort of works, but I cant seem to get it to get data from the second sheet.

Assuming "Sheet2" is the name of the 2nd sheet that contains the cell A10 that you want to test, then:


Set cond1 = rg.FormatConditions.Add(xlCellValue, xlEqual, "=Sheet2!$A$10")

should work. However you have a serious bug in that every time you run worksheet_change , you create a new format condition, which adds itself to all the other format conditions you have previously created. Just because you add a new format condition does not mean all the old ones go away. Use your spreadsheet much and soon you will have hundreds of near duplicate format conditions. You need to add code to first delete the old format conditions(s) before adding the new one.

BTW, I recommend you use code tags for clarity when posting code
https://www.access-programmers.co.uk/forums/attachment.php?attachmentid=46223&stc=1&d=1358570334
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=46224&stc=1&d=1358570334

[Stimpy]

Well, that is a giant no-go then. Guess i'll struggle a bit more and see what else i can come up with!
Thanks for the help anyway. If you hadn't said that i would still be trying to make it work without knowing about that huge bug :)

[rlv]

Not necessarily. It just means that you have to consider housecleaning, and delete the old format condition when you add a new format condition.



Private Sub Worksheet_Change(ByVal Target As Range)


Dim rg As Range
Dim cond1 As FormatCondition
Set rg = Range("$B$8", Range("$B$8").End(xlDown))


For Each cond1 In rg.FormatConditions
If cond1.Type = xlExpression And cond1.Formula1 = "=Sheet2!$A$10" Then
cond1.Delete
End If
Next cond1


Set cond1 = rg.FormatConditions.Add(xlCellValue, xlEqual, "=Sheet2!$A$10")


With cond1
.SetFirstPriority
.Interior.Color = vbGreen
.Font.Color = vbWhite
.StopIfTrue = True
End With
End Sub


(not tested)