Format/Highlight a cell based on another cell color?

[bazzinga]

Hello Everyone,

I created an example of my current spreadsheet. I would like to be able that the transposed(?) cells and copy the highlight (if any) based on the Data Set. Is it possible?
(in this case, A7 will automatically copy the highlight, and if not copy highlight)

Kind Regards,
B

 

[rlv01]

Not sure what your question is. The Excel Transpose function will not carry over the formatting. Copy/Paste using the "Transpose" option will copy the formatting too. You can automate the copy/transpose-paste by using a VBA macro that you either write or create by using the Excel Macro Recorder. If there is conditional formatting at play in the source cells, that may or may not add some additional complexity, but essentially it is doable.

Sub Macro1()
    Dim SaveCell As Range

    Set SaveCell = ActiveCell
    With ActiveSheet
        .Range("C1:G2").Copy
        .Range("A4").PasteSpecial Paste:=xlPasteAll, Transpose:=True
        Application.CutCopyMode = False
        SaveCell.Select
    End With
End Sub

 

[bazzinga]

Not sure what your question is. The Excel Transpose function will not carry over the formatting. Copy/Paste using the "Transpose" option will copy the formatting too. You can automate the copy/transpose-paste by using a VBA macro that you either write or create by using the Excel Macro Recorder. If there is conditional formatting at play in the source cells, that may or may not add some additional complexity, but essentially it is doable.

Sub Macro1()
    Dim SaveCell As Range

    Set SaveCell = ActiveCell
    With ActiveSheet
        .Range("C1:G2").Copy
        .Range("A4").PasteSpecial Paste:=xlPasteAll, Transpose:=True
        Application.CutCopyMode = False
        SaveCell.Select
    End With
End Sub

Hi,

Sorry I got a bit lost. Hopefully I can explain it better:
If Cell F2 (or any cell in Line 2) is highlighted, Can I get some indication of the highlight on column A when transposing the data from Line 2. So in this case, A7 would highlight.

I never used VBA, but if I can get some pointer, maybe I can google the rest to understand how to use VBA better.

 

[rlv01]

If Cell F2 (or any cell in Line 2) is highlighted, Can I get some indication of the highlight on column A when transposing the data from Line 2. So in this case, A7 would highlight.

Yes, and that is what the VBA code sample I posted does.

Hi,
I never used VBA, but if I can get some pointer, maybe I can google the rest to understand how to use VBA better.

There are some pretty good youtube video tutorials designed for complete beginners that would be a good place to start. Search for "learn VBA" and take your pick. Once you learn how to use the debugger you can single step through the code sample I posted and watch what it does.

 

[bazzinga]

Yes, and that is what the VBA code sample I posted does.

View attachment 103092




There are some pretty good youtube video tutorials designed for complete beginners that would be a good place to start. Search for "learn VBA" and take your pick. Once you learn how to use the debugger you can single step through the code sample I posted and watch what it does.

Thank you! Will give it a try