Stimpy
11-19-2018, 07:14 AM
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? :)
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? :)