maybe something like
Sub ertertMax2()
Dim rngTestArea As Range, hide1!
Dim i&, r As Range
Set rngTestArea = Range("A4,D4,A6,D6")
For i = 1 To Range("I4").Value
hide1 = WorksheetFunction.Large(rngTestArea, i)
For Each r In rngTestArea
If r.Value = hide1 Then r(1, 3).Interior.Color = vbYellow
Next r
Next i
'1. When the cell is colored yellow, i want to set visibility of the
'shape object over the yellow cell to False temporarily which will
'be set to True later by another macro
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If shp.TopLeftCell.Interior.Color = vbYellow Then
shp.Visible = msoFalse
Else
shp.Visible = msoTrue
End If
Next
'2. do some activity to change the set random values
rngTestArea.Calculate
End Sub
about r(1 ,3)
Sub tt()
Dim r As Range
Set r = Range("A6")
MsgBox r(1, 3).Address 'C6 r(1, 3) - 1 is row index, 3 is column index
' it's the same
MsgBox r.Cells(1, 3).Address 'C6
' it's the same
MsgBox r.Offset(0, 2).Address 'C6
End Sub
Bookmarks