none
VBA - Copy the fill color of a Excel cell to PowerPoint RRS feed

  • Question

  • Hi all,

    I have build a program in Excel VBA that builds a PowerPoint file using data from cells and graphs from the Excel file.

    First I add a table:

    Set Table = newPowerPoint.ActivePresentation.Slides(1).Shapes.AddTable(11, 7)

    With this table, I configure everything as I want to.

    One of the things I managed, was to copy the text from a cell and paste it in specific cells. One example:

    With table

    .Table.Cell(2, 7).Shape.TextFrame.TextRange.Text = WorkBook.Worksheets(1).Cells(3, 63)

    End With

    All of this works, but now I want something more.

    Some of the cells have a back color. I want VBA also to copy this color and paste it.

    How can I do this?

    Thanks

    Ganesh

    Wednesday, January 18, 2017 1:49 PM

Answers

  • Hi Deepak,

    I managed to do it the following way:

    ColorBetter = RGB(155, 187, 89)
    ColorNoChange = RGB(79, 129, 188)
    ColorWorse = RGB(192, 80, 77)
    
    For i = 1 To 10
                    Item = WSIndex.Cells(i + 2, 63)
                    If Item = "BETTER" Then
                    .Table.cell(i + 1, 7).Shape.Fill.ForeColor.RGB = ColorBetter
                    ElseIf Item = "NO CHANGE" Then
                    .Table.cell(i + 1, 7).Shape.Fill.ForeColor.RGB = ColorNoChange
                    ElseIf Item = "WORSE" Then
                    .Table.cell(i + 1, 7).Shape.Fill.ForeColor.RGB = ColorWorse
                    End If
                Next


    Monday, January 23, 2017 9:08 AM

All replies

  • Hi ganeshgebhard,

    you can try to use copy paste instead of directly assigning the value.

    then you can use PasteSpecial.

    with the help of that you can copy the formatting.

    example:

    With Worksheets("Sheet1") 
     .Range("C1:C5").Copy 
     .Range("D1:D5").PasteSpecial _ 
     Operation:=xlPasteSpecialOperationAdd 
    End With
    

    Reference:

    Range.PasteSpecial Method (Excel)

    XlPasteType Enumeration (Excel)

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 19, 2017 2:33 AM
    Moderator
  • Hi Deepak,

    The table is build from scratch with data from Excel. This data cannot be copied that simple, because it is spread out in the workbook. Only for some of the cells, I need the fill to come along.

    Hope this helps.

    Ganesh

    Thursday, January 19, 2017 7:03 AM
  • Hi ganeshgebhard,

    I can see that you are not able to implement the last suggestion.

    you had mentioned that you want to fetch the color for only some cells.

    so you can try to save the cells in array which you want to check for the color.

    then you can use the loop and get the color index.

    then you can assign the color in powerpoint by this color index for particular cell.

    Example:

    Sub demo()
    Dim rng As Range, cell As Range
    Set rng = Range("A1:A5")
    For Each cell In rng
    Debug.Print (cell.Address & " = " & cell.Interior.ColorIndex)
    Next cell
    End Sub
    

    Output:

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 20, 2017 8:23 AM
    Moderator
  • Hi Deepak,

    Thanks a lot!

    I did some rewriting, and the code works partially. I'm having one more problem:

    These are the cells where the color have to come from (part of the whole)

    Now, when I add a table to PowerPoint with this data it looks like:

    These colors can change each month, so this isn't always specifically in this order.

    I rewritten the code as follow:

    Dim rng As Range, cell As Range

    Set rng = Range("BI1:BK12")

    ColorBetter = RGB(155, 187, 89) ColorNoChange = RGB(79, 129, 188) ColorWorse = RGB(192, 80, 77) For Each cell In rng If cell = "BETTER" Then .Table.cell.Shape.Fill.ForeColor.RGB = ColorBetter End If Next cell

    This however does not seem to work properly. 

    What I want is that the program looks at  the first cell, see which text it has (this works) and then use this text to set the correct color for the cell in PowerPoint. This last thing is the problem.

    I hope you can help me with this last thing.

    Ganesh

    Friday, January 20, 2017 12:29 PM
  • Hi ganeshgebhard,

    if possible then post your code here.

    so that we can try to make a test with it on our side.

    above code does not looks like a complete code.

    also it looks like you are executing this code in excel and try to set color in powerpoint table.

    can you tell me how you refer to the powerpoint table.

    I did not see any code for that above.

    you can try to create object of table or shape and then try to set a color of it.

    Public Sub demo()
    
        Dim X As Integer
        Dim Y As Integer
        Dim oTbl As Table
    
        Set oTbl = ActiveWindow.Selection.ShapeRange(1).Table
    
            For X = 1 To oTbl.Columns.Count
    
                For Y = 1 To oTbl.Rows.Count
    
                    With oTbl.Cell(Y, X)
    
                        If .Selected <> False Then
                         
                            .Shape.Fill.ForeColor.RGB = RGB(100, 150, 200)
                    End With
                Next
            Next
        End Sub

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 23, 2017 5:24 AM
    Moderator
  • Hi Deepak,

    I managed to do it the following way:

    ColorBetter = RGB(155, 187, 89)
    ColorNoChange = RGB(79, 129, 188)
    ColorWorse = RGB(192, 80, 77)
    
    For i = 1 To 10
                    Item = WSIndex.Cells(i + 2, 63)
                    If Item = "BETTER" Then
                    .Table.cell(i + 1, 7).Shape.Fill.ForeColor.RGB = ColorBetter
                    ElseIf Item = "NO CHANGE" Then
                    .Table.cell(i + 1, 7).Shape.Fill.ForeColor.RGB = ColorNoChange
                    ElseIf Item = "WORSE" Then
                    .Table.cell(i + 1, 7).Shape.Fill.ForeColor.RGB = ColorWorse
                    End If
                Next


    Monday, January 23, 2017 9:08 AM
  • Hi ganeshgebhard,

    from your last reply I can see that you had solved your issue.

    this thread is still open.

    so please mark your last post as an answer so that we can close this thread.

    otherwise this thread will remain open.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 24, 2017 2:48 AM
    Moderator