VB simple code help

[rc39452]

Can someone help me with this? I'm got this information from another post and trying to use it. I need to color all of the entries that begin with "HQARM 3" "HQARM 4" "HQARM 8" "HQARM 9" any suggestion will be greatly appreciated.

If Left(ActiveCell, 7) = "HQARM 3" Then
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid

If Left(ActiveCell, 7) = "HQARM 4" Then
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid

If Left(ActiveCell, 7) = "HQARM 8" Then
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid

If Left(ActiveCell, 7) = "HQARM 9" Then
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid

 

[Norie]

What problem are you actually having?

Does this work?

Dim strTest
Dim c As Range

    Set c = ActiveCell
    strTest = Left(c, 7)
    Select Case strTest
        Case "HQARM 3", "HQARM 4", "HQARM 8", "HQARM 9"
        
            With c.Interior
            .ColorIndex = 6
            .Pattern = xlSolid
            End With
        Case Else
            ' do nothing
    End Select

BTW do you really need to use VBA, can't you use Conditional formatting?

 

[rc39452]

I will try it now.....

No, i don't have to use VB. I can use conditional formating do you have any suggestions?

 

[Norie]

Well you would need to use a formula.

Is it all entries beginning HQARM or is it only the ones mentioned in your post?

 

[rc39452]

that didn't seem to work. I'm copying and pasting it into existing code.
I probably have to send you the entire code for you to make the ajustments.

I think the conditional formatting may be better?

 

[Norie]

Well for the conditional formatting you would use the Formula is option with a formula like this:

=LEFT(A1,5)="HQARM"

 

[rc39452]

how do you make the background turn colors.

 

[Norie]

Goto Format... on the Conditional Formaatting form and select the Patterns tab.

You can set your colour/pattern there.

 

[rc39452]

thanks for all your help