Preserve all formatting

[UnitedCloud01]

Hi All

I have a cell (Y6) that returns a value:

IF($K6="","",IF($K6>=61,DATA!$A$91,IF($K6>=51,DATA!$A$90,IF($K6>=1,DATA!$A$89,""))))

I have formatted the results in DATA!$A as I would like it to appear in the working sheet.:

[TABLE="width: 511"]
<colgroup><col width="682" style="width: 511pt; mso-width-source: userset; mso-width-alt: 24234;"> <tbody>[TR]
[TD="class: xl67, width: 682, bgcolor: red"]RED GROUPS & WORKERS:
- Severe emotional /behavioural dysregulation
- Severe distress intolerance and mood disturbance
- Brain area: Brainstem/Diencephalon
- CGAF ≤ 50[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 682, bgcolor: #FFC000"]AMBER GROUPS & WORKERS:
- Moderate emotional/ behavioural dysregulation
- Moderate mood disturbance
- Brain area: Limbic
- CGAF 50-60[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 682, bgcolor: #92D050"]GREEN GROUPS & WORKERS:
- Mild emotional/ behavioural dysregulation
- Mild mood disturbance
- Brain area: Prefrontal Cortex
- CGAS 60-75[/TD]
[/TR]
</tbody>[/TABLE]

I have tried the following VBA:

[FONT=Calibri][SIZE=3][COLOR=#000000]Range("Y6").Value = Range("[SIZE=2][COLOR=#222222]DATA!$A$91[/COLOR][/SIZE]").Value<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]For i = 1 To Range("[SIZE=2][COLOR=#222222]DATA!$A$91[/COLOR][/SIZE]").Characters.Count<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]   Range("Y6").Characters(i, 1).Font.Bold =Range("[SIZE=2][COLOR=#222222]DATA!$A$91[/COLOR][/SIZE]").Characters(i, 1).Font.Bold<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]   Range("Y6").Characters(i, 1).Font.Color =Range("[SIZE=2][COLOR=#222222]DATA!$A$91[/COLOR][/SIZE]").Characters(i, 1).Font.Color<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Range("Y6").Characters(i, 1).Font.FontStyle = Range("[SIZE=2][COLOR=#222222]DATA!$A$91[/COLOR][/SIZE]").Characters(i,1).Font.FontStyle<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]   Range("Y6").Interior.ColorIndex =Range("[SIZE=2][COLOR=#222222]DATA!$A$91[/COLOR][/SIZE]").Interior.ColorIndex<o:p></o:p>[/COLOR][/SIZE][/FONT]

This code does indeed preserve the formatting, in this instance from DATA!$A$91 but I cannot work out how to alter the code to reflect the change in the result in cell "Y6".

This code needs to work for all active cells in column "Y".

I look forward to your assistance.

Regards

Scott

 

[Fluff]

I'm not quite sure why it's not working, but lets try another approach.

Sub CopyData()

Application.EnableEvents = False

    Select Case ActiveCell.Value
        Case Is <= 50
            Sheets("Data").Range("A89").copy Range("Y" & ActiveCell.Row)
        Case 51 To 60
            Sheets("Data").Range("A90").copy Range("Y" & ActiveCell.Row)
        Case Is >= 61
            Sheets("Data").Range("A91").copy Range("Y" & ActiveCell.Row)
    End Select

Application.EnableEvents = True

End Sub

This needs to go in a standard module, & will need to be run manually.
Select a cell in col K with a value in & then run the code.

 

[UnitedCloud01]

HI Fluff

Thanks for the help.

I will not worry about the formatting as it does not seem possible.

Thanks again

Scott