Hi yall, I'm working on a spreadsheet to track & map out progress for players of the Legend of Zelda Randomizer. A gamer has got into the code for the original NES Zelda game and randomized many aspects of the game including the overworld's layout (Dungeon/Shop locations) and shapes of Dungeons.
Screen shot of the spreadsheet: https://i.imgur.com/lLmqWPO.png
I've got the worksheet checking the ranges for the Overworld section and the Dungeon mapping section with:
Private Sub Worksheet_Change(ByVal Target As Range)
'Overworld
If Not Intersect(Target, Range("A1:DQ8")) Is Nothing Then
Call Overworld
End If
If Not Intersect(Target, Range("F10:DE42")) Is Nothing Then
Call Mapper
End If
End Sub
For the dungeons I've changed the cell color based on the number or letter input with:
Sub Mapper()
Dim i, j
For i = 10 To 42
For j = 6 To 110
If Cells(i, j).Value = "" Then Cells(i, j).Interior.Color = RGB(0, 0, 0) 'If empty = Black
If Cells(i, j).Value = "1" Then Cells(i, j).Interior.Color = RGB(0, 255, 255) 'Level 1
If Cells(i, j).Value = "2" Then Cells(i, j).Interior.Color = RGB(0, 0, 255) 'Level 2
......
If Cells(i, j).Value = "A" Then Cells(i, j).Interior.Color = RGB(250, 51, 51) 'Staircase
If Cells(i, j).Value = "B" Then Cells(i, j).Interior.Color = RGB(225, 255, 255)
......
If Cells(i, j).Value = "G" Then Cells(i, j).Interior.Color = RGB(204, 255, 220) 'Gannon's room
If Cells(i, j).Value = "Z" Then Cells(i, j).Interior.Color = RGB(185, 102, 255) 'Zelda's room
Next j
Next i
End Sub
Its working (hence the shapes colored in) but takes longer than I'd like as it goes through the whole range. How can I make this work quicker ?
Is there a way for the Private Sub tracking the range to send the location of the change to the macro ? That way the macro would just need to look at the one changed cell instead of going through the entire range, right ?
Screen shot of the spreadsheet: https://i.imgur.com/lLmqWPO.png
I've got the worksheet checking the ranges for the Overworld section and the Dungeon mapping section with:
Private Sub Worksheet_Change(ByVal Target As Range)
'Overworld
If Not Intersect(Target, Range("A1:DQ8")) Is Nothing Then
Call Overworld
End If
If Not Intersect(Target, Range("F10:DE42")) Is Nothing Then
Call Mapper
End If
End Sub
For the dungeons I've changed the cell color based on the number or letter input with:
Sub Mapper()
Dim i, j
For i = 10 To 42
For j = 6 To 110
If Cells(i, j).Value = "" Then Cells(i, j).Interior.Color = RGB(0, 0, 0) 'If empty = Black
If Cells(i, j).Value = "1" Then Cells(i, j).Interior.Color = RGB(0, 255, 255) 'Level 1
If Cells(i, j).Value = "2" Then Cells(i, j).Interior.Color = RGB(0, 0, 255) 'Level 2
......
If Cells(i, j).Value = "A" Then Cells(i, j).Interior.Color = RGB(250, 51, 51) 'Staircase
If Cells(i, j).Value = "B" Then Cells(i, j).Interior.Color = RGB(225, 255, 255)
......
If Cells(i, j).Value = "G" Then Cells(i, j).Interior.Color = RGB(204, 255, 220) 'Gannon's room
If Cells(i, j).Value = "Z" Then Cells(i, j).Interior.Color = RGB(185, 102, 255) 'Zelda's room
Next j
Next i
End Sub
Its working (hence the shapes colored in) but takes longer than I'd like as it goes through the whole range. How can I make this work quicker ?
Is there a way for the Private Sub tracking the range to send the location of the change to the macro ? That way the macro would just need to look at the one changed cell instead of going through the entire range, right ?