I am quite a novice when it comes to VBA and could use some help with a workbook I have.
I have an Excel workbook containing several worksheets. Each sheet has a range of cells containing references to other ranges, ex; AY3 = “C14:D16.” This column contains conditional formatting based upon values in cells of an additional
range.
I would like to find out if anyone can help with VBA code that will loop through the array
and copy the formatting of each cell within the first range with the ability to pass that formatting to the range value given in each cell. I have found numerous answers on how to copy conditional formatting, but need to figure out how to loop through
the array and use the range values given. I also need to have this code update each time a new selection is made.
Here is what I have come up with so far:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Integer
Dim MyTarget As Range
Dim MyRange As Range
Set MyRange = (AY2)
ActiveSheet.Unprotect
' Loop through the source range
For r = 2 To 101
MyRange = Range("AY" & r)
If MyRange.Value = "" Then
ActiveSheet.Protect
Exit Sub
End If
' Get the value of each cell
MyTarget = MyRange.Value
' Copy the conditional formatting of each cell to the range specified
MyRange.Copy
MyTarget.PasteSpecial Paste:=xlPasteFormats
Next r
ActiveSheet.Protect
End Sub
I know it probably isn't pretty, and there seems to be a couple bugs to kill. Many thanks for any help you can provide.
