Need some syntax help, simple loop I think

[Mr B]

Howdy,

I have programmed in Excel in a long time and am not sure where to begin...

I have some data in 5 column such as:
Name, City, X-coord, Y-coord, Item

I want to run through this data and create a map in the second sheet where
at the given X, Y coordinates for each person, it colors that cell a certain
color based on the "Item" and then creates a Comment for the cell containing
the Name and City.

I know this should be pretty darn easy but I'm rusty and need some guidance.
Some things I know I need are:

How to set the fill color via a command.
How to insert a comment via a command.
Basically everything to get me started.

Thanks for any and allhelp.

 

[Chip Pearson]

What type of data is contained in the X-coord and Y-coord values and how are
these values mapped to cells or locations on the second sheet? How is the
Item value related to a color that is to be used on the second sheet?


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Mr B]

Hi Chip,

The data would look like this:

John, Smithville, 13, 22, Coal
Jane, Doesville, 45, 11, Water

I figured it'd be easiest to just have 5 IF statements to set the colors...

If (E,CurrentRow) = Coal then set the color to black.
If (E,CurrentRow) = Water then set the color to blue
etc


Nothing is mapped to the second sheet at all. I just want to use the first
100 rows and columns of the 2nd sheet to be the "map".

So in the example above, it would go to Column 13, Row 22, fill it with
Black and add a Comment saying John - Smithville.

Then it would go onto the next one...

I'm good with taking existing scripts and tweaking them but I don't have
anything to start with for this...

Thanks!

 

[Dick Kusleika]

I'm good with taking existing scripts and tweaking them but I don't have
anything to start with for this...

Watch for line wrap:

Sub MapData()

Dim rCell As Range, rRng As Range

Sheet2.UsedRange.Clear

Set rRng = Sheet1.Range("A1", Sheet1.Range("A1").End(xlDown))

For Each rCell In rRng.Cells
With Sheet2.Cells(rCell.Offset(0, 3).Value, rCell.Offset(0,
2).Value)
Select Case rCell.Offset(0, 4).Value
Case "Coal"
.Interior.Color = vbBlack
Case "Water"
.Interior.Color = vbBlue
Case "Solar"
.Interior.Color = vbCyan
Case "Wind"
.Interior.Color = vbYellow
Case "Gas"
.Interior.Color = vbMagenta
End Select

'.Comment.Text = rCell.Offset(0, 0).Value & "-" &
rCell.Offset(0, 1).Value
'.Comment.Visible = True
.AddComment rCell.Offset(0, 0).Value & "-" & rCell.Offset(0,
1).Value
End With
Next rCell
End Sub

 

[Mr B]

OK, that's perfect.

Thanks a TON!

Watch for line wrap:

Sub MapData()

Dim rCell As Range, rRng As Range

Sheet2.UsedRange.Clear

Set rRng = Sheet1.Range("A1", Sheet1.Range("A1").End(xlDown))

For Each rCell In rRng.Cells
With Sheet2.Cells(rCell.Offset(0, 3).Value, rCell.Offset(0,
2).Value)
Select Case rCell.Offset(0, 4).Value
Case "Coal"
.Interior.Color = vbBlack
Case "Water"
.Interior.Color = vbBlue
Case "Solar"
.Interior.Color = vbCyan
Case "Wind"
.Interior.Color = vbYellow
Case "Gas"
.Interior.Color = vbMagenta
End Select

'.Comment.Text = rCell.Offset(0, 0).Value & "-" &
rCell.Offset(0, 1).Value
'.Comment.Visible = True
.AddComment rCell.Offset(0, 0).Value & "-" & rCell.Offset(0,
1).Value
End With
Next rCell
End Sub