CODEWITHSUNDEEP
excelvba
Mogaru
Mogaru

Reputation: 1

How to copy colors of cell range to other cell range using VBA

I've made a calendar in Excel for my work shifts. e.g,

C55:I55 contains the dates of a week (1-jun, 2-jun, 3-jun etc.) C56:I56 contain shifts, which are colored based on conditional formatting (I work early/late/night shifts which have different colors)

Now, I want the date row (C55:I55) to copy the colors of the shifts row (C56:I56). I've got some basic VBA code that can do this for one cell, but how can I make it work for a row?

I've already looked on the web and StackOverflow for a solution, but I can't find the specific solution I'm looking for.

This is the code I currently have:

Private Sub Worksheet_Calculate()
    Range("C55").Interior.Color = Range("I55").DisplayFormat.Interior.Color
End Sub

-

EDIT

So, with some help and tweaking, I got it to work using this code:

Private Sub Worksheet_Calculate()

Dim i As Long

    With ThisWorkbook.Worksheets("Rooster 2020")

        For i = 3 To 9
           .Cells(63, i).Interior.Color = .Cells(64, i).DisplayFormat.Interior.Color
        Next i

    End With

End Sub

Now I'm just curious, is there a way to include more lines than just "63" and "64" in the above example?

Upvotes: 0

Views: 2877

Answers (3)

L42
L42

Reputation: 19737

As commented, you don't need VBA to do this. You can do conditional formatting by just extending the range where it is applied.

Consider you have a data like this:

enter image description here

You need to do the following steps:

  1. Select C56 and create the conditional formatting using the rule type Use a formula to determine which cells to format. So based on your comment, your formula should look like this: =AND(C$56>=1,C$56<=10) (for blue) and =AND(C$56>=11,C$56<=20) (for red). It is important to note though that in the formula, we lock the row (absolute reference) while the column is moving (relative reference) using C$56 meaning it will always refer to the value at row 56 but column will adjust. So the formatting will be based on C$56, D$56, E$56... and I$56 values.

    enter image description here

  2. Now after creating the rules, you need to edit the range where it applies to.

    enter image description here

  3. After changing the range, press apply.

    enter image description here

Take note that although you created the conditional formatting on C56, you applied it on $C$55:$I$56. After doing all those steps, you'll see the result like below.

enter image description here

Upvotes: 0

Error 1004
Error 1004

Reputation: 8230

With this code you just copy the interior color:

Code:

Option Explicit

Sub test()

    Dim y As Long, i As Long
    Dim arrLines As Variant

    'Colored Lines
    arrLines = Array(1, 4, 23)

    With ThisWorkbook.Worksheets("Sheet1")

        For y = LBound(arrLines) To UBound(arrLines)

            For i = 3 To 9
               'Color the next line from the line which has colot
               .Cells(arrLines(y) + 1, i).Interior.Color = .Cells(arrLines(y), i).Interior.Color
            Next i

        Next y
    End With

End Sub

Results:

enter image description here

Upvotes: 1

Damian
Damian

Reputation: 5174

Conditional formatting <> .Interior.Color so you need to copy paste formats, like this:

Private Sub Worksheet_Calculate()
    'This will copy the conditional formatting
    'Change the sheet name. ThisWorkbooks points to the workbook holding the code
    With ThisWorkbook.Sheets("MySheet")
        .Range("C55").Copy
        .Range("I55").PasteSpecial xlPasteFormats
    End With

    'This will only copy the color from the cell
    With ThisWorkbook.Sheets("MySheet")
        .Range("I55").DisplayFormat.Interior.ColorIndex = .Range("C55").DisplayFormat.Interior.ColorIndex
    End With

End Sub

Upvotes: 2

Related Questions