Answered by:
Copying Conditional formatting from one range to another

Question
-
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:=xlPasteFormatsNext 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.
- Moved by Youen Zen Tuesday, February 5, 2013 5:10 AM From visual basic
Monday, February 4, 2013 7:12 PM
Answers
-
This forum is for VB Net, not VBA.
Maybe a moderator can move the thread for you.
Please call me Frank :)
- Marked as answer by wingrider54 Tuesday, June 18, 2013 4:28 PM
Monday, February 4, 2013 7:31 PM
All replies
-
This forum is for VB Net, not VBA.
Maybe a moderator can move the thread for you.
Please call me Frank :)
- Marked as answer by wingrider54 Tuesday, June 18, 2013 4:28 PM
Monday, February 4, 2013 7:31 PM -
Sorry about that. I can't seem to find the right forum for any of my postings :-)Monday, February 4, 2013 7:34 PM
-
Sorry about that. I can't seem to find the right forum for any of my postings :-)
This is old so I'm not sure if it's still valid but worth a try:
http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/c147bae1-c9db-4ae8-9557-43713004cc94
Please call me Frank :)
Monday, February 4, 2013 7:37 PM