Using excel as a mapping tool

[Brodegard]

This thread is regarding how format a color on a cell in a map of a building I drew in excel.

In my map, each square foot in the facility corresponded to a cell in excel. For instance, the spot right by a door in the building might be "FA 130," where FA indicates the column and "130" indicates the row.

Now I need to be able to use conditional formatting to change the color of the cell in my map based on a result.

For example, my spreadsheet would read:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column
[/TD]
[TD]Row
[/TD]
[TD]Result
[/TD]
[/TR]
[TR]
[TD]FA
[/TD]
[TD]130
[/TD]
[TD]Negative
[/TD]
[/TR]
[TR]
[TD]BP
[/TD]
[TD]252
[/TD]
[TD]Positive
[/TD]
[/TR]
</tbody>[/TABLE]

Then, on my map I would want "FA 130" to show up red and "BP 252" to show up green.

Any ideas how I might be able to accomplish this?

 

[Fishboy]

This thread is regarding how format a color on a cell in a map of a building I drew in excel.

In my map, each square foot in the facility corresponded to a cell in excel. For instance, the spot right by a door in the building might be "FA 130," where FA indicates the column and "130" indicates the row.

Now I need to be able to use conditional formatting to change the color of the cell in my map based on a result.

For example, my spreadsheet would read:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column[/TD]
[TD]Row[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]FA[/TD]
[TD]130[/TD]
[TD]Negative[/TD]
[/TR]
[TR]
[TD]BP[/TD]
[TD]252[/TD]
[TD]Positive[/TD]
[/TR]
</tbody>[/TABLE]

Then, on my map I would want "FA 130" to show up red and "BP 252" to show up green.

Any ideas how I might be able to accomplish this?

Hi Brodegard, welcome to the boards.

If you are able to explain the logic behind what makes a cell reference positive or negative you may stand a better chance of getting some answers, or will there be a big table full of which references are which?

 

[Brodegard]

Hi Brodegard, welcome to the boards.

If you are able to explain the logic behind what makes a cell reference positive or negative you may stand a better chance of getting some answers, or will there be a big table full of which references are which?

Thanks for responding Fishboy,

This is for an environmental testing program. So if the cell reference is positive, it means the environment tested positive, and if the cell reference is negative, the reverse. The program is quite extensive, so there will be hundreds of cell references, all with "positive" or "negative."

Does that help?

 

[Eric W]

If you are willing to change your data table a little, you can do something like this:

Excel 2010

	A	B	C	D	E	F	G	H
$A$1	Negative
$A$2	Positive
$A$3	Positive
$B$1	Negative
$B$2	Negative
$B$3	Positive
$C$1	Positive
$C$2	Negative
$C$3	Negative

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FF0000"]Negative[/TD]
[TD="bgcolor: #FF0000"]Negative[/TD]
[TD="bgcolor: #00B050"]Positive[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #00B050"]Positive[/TD]
[TD="bgcolor: #FF0000"]Negative[/TD]
[TD="bgcolor: #FF0000"]Negative[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #00B050"]Positive[/TD]
[TD="bgcolor: #00B050"]Positive[/TD]
[TD="bgcolor: #FF0000"]Negative[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

PP1.2016​

In other words, put the absolute cell reference in 1 cell. You can still do it with the separate columns, but the formula is more complicated.

1) Open your worksheet
2) Select the range corresponding to your building layout. For this example, I assume the upper left corner is A1. Change the formula below to show the actual upper left corner.
3) Click Conditional Formatting --> New Rule --> Use a formula...
4) Enter this formula for the Negative (red) format:
=VLOOKUP(CELL("address",A1),$G$1:$H$9,2,FALSE)="Negative"
5) Choose a red fill color
6) Repeat 3-5 with this formula:
=VLOOKUP(CELL("address",A1),$G$1:$H$9,2,FALSE)="Positive"

Let me know if this will work for you.

 

[Fishboy]

Thanks for responding Fishboy,

This is for an environmental testing program. So if the cell reference is positive, it means the environment tested positive, and if the cell reference is negative, the reverse. The program is quite extensive, so there will be hundreds of cell references, all with "positive" or "negative."

Does that help?

I would imagine that if there are hundreds of cells that need evaluating / updating that a VBA solution would probably be best.

Now I understand why a cell reference is positive or negative, can you explain where this distinction can be found? Is there a big table as per your first post containing column / row references as well as their positive / negative status?

 

[Brodegard]

I would imagine that if there are hundreds of cells that need evaluating / updating that a VBA solution would probably be best.

Now I understand why a cell reference is positive or negative, can you explain where this distinction can be found? Is there a big table as per your first post containing column / row references as well as their positive / negative status?

Fishboy,

You are correct - On a monthly basis I download a separate excel spreadsheet that tells me what the column / row references are, and whether the result is positive or negative. The table has maybe 300 entries with column / row along with the positive / negative status.

 

[Fishboy]

Fishboy,

You are correct - On a monthly basis I download a separate excel spreadsheet that tells me what the column / row references are, and whether the result is positive or negative. The table has maybe 300 entries with column / row along with the positive / negative status.

OK, now that I have all that cleared up, try out the following code using COPIES of your workbooks. This assumes that your map is in a workbook called Map.xlsm (you can update this as required), the list is in a workbook called List.xlsm (you can update as required), that in both documents we are only dealing with the first sheet, and this code is added to the backend of the Map workbook:

Sub UpdateMap()
' Disables screen updating to reduce flicker
Application.ScreenUpdating = False
' Defines variables
Dim Cell As Range, cRange As Range, combAdd As String, Outcome As String, Wbk1 As Workbook, Wbk2 As Workbook
' Sets Wbk1 as the workbook containing the list (update as required)
Set Wbk1 = Workbooks("List.xlsm")
' Sets Wbk2 as the workbook containing the map (update as required)
Set Wbk2 = Workbooks("Map.xlsm")
' Defines LastRow as last row of column A of the List containing data
LastRow = Wbk1.Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
' Sets the check range as A2 to the last row of A of the List sheet
Set cRange = Wbk1.Sheets(1).Range("A2:A" & LastRow)
' For each cell in the list
    For Each Cell In cRange
' Set the combined address as the cell value and the adjacent cell value
        combAdd = Cell.Value & Cell.Offset(0, 1).Value
' Set the outcome as the cell value 2 columns over
            Outcome = Cell.Offset(0, 2).Value
' Select the range from the combined address from Wbk1
                    Wbk2.Sheets(1).Range(combAdd).Select
' If outcome is Netagive then...
                        If Outcome = "Negative" Then
' Fill the cell red
                            Selection.Interior.ColorIndex = 3
' Else if the outcome was positive then...
                        ElseIf Outcome = "Positive" Then
' Fill the cell green
                            Selection.Interior.ColorIndex = 4
                        End If
' Move to the next cell in the list
    Next Cell
' Re-enable screen updating
Application.ScreenUpdating = True
End Sub