CODEWITHSUNDEEP
excelvbacolorsformattingpowerpoint
PhilHibbs
PhilHibbs

Reputation: 943

How to get the actual color of a negative cell?

Some of my cells are formatted as #,##0;[Red](#,##0) which makes negative numbers show up in red. I want to copy the contents and formatting into PowerPoint. How can I identify the colour that a cell is displayed in, so that I can copy the visible appearance? I don't want to have to re-implement the logic of parsing the format and doing the negative decision if I don't have to, as not all my cells will be formatted that way.

If I can't do it by explicitly setting the colours in VBA, is it possible to use copy and paste methods to copy a cell from Excel into a cell in a table in PowerPoint? Actually this would be better than doing it longhand - I have tried but I can't work out at what object nesting level to do the paste.

I've found loads of references on how to paste Excel ranges in as a new table, but none on pasting individual cells with formatting into an existing PowerPoint table. Pasting at the TextRange level does not paste the colours, but it does paste other settings such as bold.

Upvotes: 0

Views: 1234

Answers (1)

David Zemens
David Zemens

Reputation: 53623

is it possible to use copy and paste methods to copy a cell from Excel into a cell in a table in PowerPoint?

I don't think Steve's suggestion works for Custom Number Formats like you have done. No matter what, when I query that cell's .Font.ColorIndex property (for example), the return value is 1, even if the number is negative and it appears red, which should yield 3.

Without parsing out the format conditions, I'm not sure this is possible. The number format rules seem not to transfer to PowerPoint, no matter what I have tried.

However, this may be an acceptable solution. Change your number format to:

#,##0;(#,##0)

Then instead of using custom format to do the color, apply a conditional format rule to make the cell's text red if the value is negative.

Example given for a single cell range:

Sub Test()
Dim r As Range
Dim ppt As Object
Dim pres As Object
Dim sld As Object
Dim tbl As Object
Dim tblCell As Object

Set r = [A3]

Set ppt = CreateObject("PowerPoint.Application")
Set pres = ppt.Presentations.Open("c:\presentation.pptx")
Set sld = pres.Slides(1)
Set tbl = sld.Shapes(1) 'Table in PowerPoint
Set tblCell = tbl.Table.Cell(1, 1).Shape 'Cell/shape you paste in to

r.Copy

tblCell.Select

ppt.CommandBars.ExecuteMso "PasteSourceFormatting"

End Sub

Alternatively, force the cell's color to red:

Set r = [A3]
If r.Value < 0 Then 
    r.Font.ColorIndex = 3
Else 
    r.Font.ColorIndex = 1
End If

Upvotes: 1

Related Questions