Highlighting cells and arrays

Peteor

Board Regular
Joined
Mar 16, 2018
Messages
132
Ok, so I would like to edit the following:

If ActiveSheet.Name = "Example 1" Then
Worksheets("Example 2").Activate
Else
End If


With CreateObject("scripting.dictionary")
For i = 1 To UBound(Ary)
.Item(Ary(i, 1)) = Ary(i, 2)
Next i
For Each Cl In Range("M2", Range("M" & Rows.Count).End(xlUp))
Cl.Offset(, 2).Value = .Item(Cl.Value)
Cl.Offset(, 4).Value = .Item(Cl.Offset(, 1).Value)
Next Cl


End With

This code takes in values in the form of an Array for columns B and C of Worksheet Example 1, and pastes those values to corresponding cells in Worksheet Example 2. This is a functional code, and that is not my question. I am having trouble understanding this code, it was provided to me by "Fluff" whom is smarter than I am at Excel. It was for a question which is unrelated to this one. I would like to make a macro which takes values from columns B & D of Worksheet Example 1 rather than columns B & C, and does the same task.

If a cell in column D of worksheet Example 1 is blank, do nothing.
I would like to highlight cells where a value is added.

Does anyone have any thoughts? If anyone wanted to add comments also, I would love that. Hopefully one day I will be able to understand code on this level. My code tends to be much less sophisticated and much more clunky.
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Peteor

Board Regular
Joined
Mar 16, 2018
Messages
132
OOPS! Forgot to list a crucial line.


Ary = Range("B:C1000").Value2

If ActiveSheet.Name = "Example 1" Then

Worksheets("Example 2").Activate
Else
End If


With CreateObject("scripting.dictionary")
For i = 1 To UBound(Ary)
.Item(Ary(i, 1)) = Ary(i, 2)
Next i
For Each Cl In Range("M2", Range("M" & Rows.Count).End(xlUp))
Cl.Offset(, 2).Value = .Item(Cl.Value)
Cl.Offset(, 4).Value = .Item(Cl.Offset(, 1).Value)
Next Cl


End With
 

Peteor

Board Regular
Joined
Mar 16, 2018
Messages
132
For those interested I found my answer. on the line starting with .item I had to change the 2 to a 3, and I had to increase the range of Ary.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,492
Messages
5,523,260
Members
409,506
Latest member
reneekeane

This Week's Hot Topics

Top