Search : in
By :

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

Last answer on Nov 3, 2009 5:58:48 pm GMT Howiedoin, on Oct 27, 2009 10:27:28 am GMT 
 Report this message to moderators

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

Configuration: Windows XP Internet Explorer 6.0

Best answers for « = Left(D2, 3) = "=" “using VBA the color » in :
VB6 Finding the RGB values of a color Show VB6 Finding the RGB values of a color Dim R as integer Dim G as integer Dim B as integer Sub FindRGB(Col As Long) R = &HFF& And Col G = (&HFF00& And Col ) \ 256 B = (&HFF0000 And Col ) \ 65536 End Sub Note: Here...
Excel vba if first char of cell is 1 then Show
Conditional Format - More than 3 colours Show
[VBA] A function that returns the color of an active MFC Show[VBA] A function that returns the color of an active MFC This function returns the value of the active format in conditional formatting. With the function below, two values can be returned. Mode = 0: the value of Interior.ColorIndex...
VBA: Finding Hdc in an Excel worksheet or UserForm ShowVBA: Finding Hdc in an Excel worksheet or UserForm Here are two small examples on how to find Hdc in a worksheet: By clicking on Sheet1 the UserForm is displayed. Put the pointer on UF, hold the left mouse button down and drag the...
[VBA] Detecting changes in cell Show[VBA] Detecting changes in cell The Event Change feature of a sheet will detects the change in the active cell but it gives no information about the content. The example given below will help you to find out if the cell was changed,...
Download FastStone Image Viewer 3.5 Show
Download Peacock Color Picker Show
Download Color Cop ShowColor Cop allows to choose a color directly in screen with a pipette and to show it in a visualisor with his code RGB (RVB). It is possible to use a magnifier for a better selection. This software is notably very practical for any webmaster...
Worksheet - Cells ShowThe Concept of a Cell A "cell" is the intersection between a line (horizontal) and a column (vertical) on a worksheet. Thus, the name of the line combined with the name of the column gives the cell's coordinates (the term address is sometimes also...
Ask your question Reply

1

Excelguru, on Oct 30, 2009 7:19:05 am GMT
  • +1

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
... A wise man once said, 'I complained that I had no shoes until I met a man who had no feet.'
I am interested in financial Modelling and custom excel development with excel macros.

   
Reply to Excelguru

2

 Howedoin, on Nov 3, 2009 5:58:48 pm GMT

Thank you so much!

   
Reply to Howedoin
Ask your question Reply
They need your help