= Left(D2, 3) = "=" “using VBA the color [Solved/Closed]

Report
-
 Howedoin -
Hello,

I am trying to use more then 3 conditional formatting and have found myself working with vba. What I am trying to do is: look-up the left three numbers (ex. 31512345678) and if “315” then color cell this (=LEFT($D2,3)="315"). I get that; however in VBA I am not able to apply the same code (multiple times).

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer



If Not Intersect(Target, Range("d1:d1000")) Is Nothing Then

Select Case Target

Case Is = Left(D2, 3) = "315"

icolor = 6

Case Is = Left(D2, 3) = "718"

icolor = 12

Case Is = Left(D2, 3) = "716"

icolor = 7

Case Is = Left(D2, 3) = "235"

icolor = 53

Case Is = Left(D2, 3) = "123"

icolor = 15

Case Is = Left(D2, 3) = "456"

icolor = 42

Case Else

'Whatever

End Select



Target.Interior.ColorIndex = icolor

End If



End Sub

2 replies

Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
Hello

Change your select statements to

Select Case Left(ActiveSheet.Range(Target.Address).Value, 3)
Case "315": icolor = 6
Case "718": icolor = 12
Case "716": icolor = 7
...
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 4222 users have said thank you to us this month

Thank you so much!