= Left(D2, 3) = "=" “using VBA the color

Solved/Closed
Howiedoin - Oct 27, 2009 at 06:27 AM
 Howedoin - Nov 3, 2009 at 12:58 PM
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

Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Oct 30, 2009 at 03:19 AM
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 so much!
0