Formating dependant on text being bold

[Pauljj]

I need this to apply to rows in A and B.

If for example A41 text is in bold, then I need to have the pattern for A41:B41 as solid grey

If A2355 is in bold then I need to have the pattern for A2355:B2355 as solid grey and so on, this macro needs to be actioned only when something is pasted into cell A1

Any help on this please ?

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long, i As Long
If Target.Address(False, False) = "A1" Then
    LR = Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        With Range("A" & i)
            If .Font.Bold = True Then
                .Resize(, 2).Interior.ColorIndex = 15
            Else
                .Resize(, 2).Interior.ColorIndex = xlNone
            End If
        End With
    Next i
End If
End Sub

 

[Pauljj]

Thanks for this, I've pasted this in to the sheet code but literally nothing happens ?

 

[VoG]

Ttpe or paste something into A1 - does anything happen?

Are macros enabled? Tools > Macro Security.

Are Events enabled? Press CTRL + G and type into the Immediate Window

Application.EnableEvents = true

 

[Pauljj]

I've made a slight change to the code as I want to paste text into A1 once I paste into A1 I want to make the appropriate colour changes, bizzarly its only working from row 11 downwards ?

Private Sub Worksheet_selectionChange(ByVal Target As Excel.Range)

Dim LR As Long, i As Long
If Target.Address(False, False) = "A1" Then
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With Range("A1" & i)
If .Font.Bold = True Then
.Resize(, 2).Interior.ColorIndex = 15
.Resize(, 2).Font.ColorIndex = 0
Else
.Resize(, 2).Interior.ColorIndex = xlNone
.Resize(, 2).Font.ColorIndex = 0
End If
End With
Next i
End If



End Sub