none
Copying Conditional formatting from one range to another RRS feed

  • 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:=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.

    • 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