Array formula populated from text strings

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Using XL 2003 & 97

What is the most efficient VBA code to:
1) read the following text layout of cell or range references
2) Go to each cell or range in the list
3) Change the cell or range of cells to a background color i.e. Yellow

U8, K8, S8, AH8, AJ8, AP8, AP10, AR8, AT8, AP14, M18, O18, E18:E22, K18:K22
S18:S22, AD18:AD22, AF18:AF22, AH18:AH22, AJ18:AJ22, AL18:AL22, AN18:AN22
AP18:AP22, AR18:AR22, AT18:AT22, U28, E28:E29, K28:K29, S28:S29, AD28:AD29

My guess is via the use of an array formula and a loop. That said, I am at
a loss as to where to begin.

TIA Dennis
 
I just recorded this.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/21/2004 by Don Guillett
'

'
Range("B17,F14,G13,G16").Select
Range("G16").Activate
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End Sub

Can be shortened to
Range("B17,F14,G13,G16").ColorIndex = 6
 
Don, thanks for your information.

Would like to be able to read a text file like below, but almost as good and
OK is copy/paste the same info into a variable via an input box. (As long as
the carrage returns are not going to cause a problem)

I think that there are two types of "input" boxes.

Which one should I use to copy/paste the Text and then have it inserted into
the formula?


i.e.
Dim MyString as String
MyString=InputBox(XXXXX, YYYYY)

then something like?: Range(MyString).ColorIndex = 6

Thanks again!

************************************
 
do you mean this one liner ? input would be n4,n5,a12:c15

Sub askforselection()
Range(InputBox("enter ranges & separate with comma")) _
..Interior.ColorIndex = 6
End Sub
 
Another way that allows you to use the mouse to select your range (click and
ctrl-click to get multiple areas).

Option Explicit
Sub askforselection2()

Dim myRng As Range
Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox(Prompt:="Point at a few cells!", Type:=8)
On Error GoTo 0

If myRng Is Nothing Then
'user cancelled--do nothing
Else
myRng.Interior.ColorIndex = 6
End If
End Sub
 
I misunderstood about the copy/paste from a text file.

But it might be good to test if you typed/pasted a valid address:

Option Explicit
Sub askforselection3()

Dim myRng As Range
Set myRng = Nothing
On Error Resume Next
Set myRng = Range(InputBox(Prompt:="Paste Away!"))
On Error GoTo 0

If myRng Is Nothing Then
'invalid range
Else
myRng.Interior.ColorIndex = 6
End If
End Sub
 
Back
Top