Format Cell On Select

[plans]

Hello,
I have a Range called "Types".
Each cell of "Types" has a certain formatting.
The "Types" is a List for "Validated Cells".
I am trying to match the Types format when a selection is made from the Drop Down Box of the validated cell.
Please see the attached workbook.
Could you please advise?
Thanks

[protonLeah]

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Found As Variant
    
    If Not Intersect(Target, Range("C11:H17")) Is Nothing Then
        Application.EnableEvents = False
        Set Found = Range("types").Find(Target.Value)
        Found.Copy
        Target.PasteSpecial _
            Paste:=xlPasteFormats
        
        Application.CutCopyMode = False
        Application.EnableEvents = True
        Set Found = Nothing
    End If
End Sub

[plans]

protonLeah,
Ingenious!
You made it so simple; it works perfect.
Thank you very much!

[YasserKhalil]

@ protonLeah
Thanks for great code
When clear the contents of any cell withing the target range there is an error message appeared and the code stopped working
"Object variable or with block variable not set" at this line

Found.Copy

[protonLeah]

Modified for cleared cells:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Found As Variant
    
    If Not Intersect(Target, Range("C11:H17")) Is Nothing Then
        Application.EnableEvents = False
        Set Found = Range("types").Find(Target.Value)
        
        If Not Found Is Nothing Then
            Found.Copy
            Target.PasteSpecial _
                Paste:=xlPasteFormats
            Application.CutCopyMode = False
            Set Found = Nothing
        Else
            With Target.Interior
                .Pattern = xlNone
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With    'target cell interior clear
        End If  'found
    End If  'in validated cells
    Application.EnableEvents = True
End Sub

[YasserKhalil]

Thanks a lot for this perfect modification
It works well now

[plans]

protonLeah,

I can't figure out what you did but it is just, perfect.
It is the best I ever have seen in this forum!
YasserKhalil --> thanks for your help too!

[plans]

protonLeah,
What I did not realize, is that when I use this, I will need to insert / delete rows and columns within the Range("C11:H17"), so this range area should be able to extend or shrink.
When I insert or delete rows, columns, then first I am getting an error message and the cells won't update their formats.
Is there an easy fix to it?
Thank you

[protonLeah]

This is just a guess since I don't know what the actual worksheet looks like:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Found As Variant, _
        ValAddress  As String

'branch to Function FindValidationCells to get the address of the block of cells
'with data validation starting at cell Cll

        ValAddress = FindValidationCells
    
    If Not Intersect(Target, Range(ValAddress)) Is Nothing Then
        Application.EnableEvents = False
        Set Found = Range("types").Find(Target.Value)
        
        If Not Found Is Nothing Then
            Found.Copy
            Target.PasteSpecial _
                Paste:=xlPasteFormats
            Application.CutCopyMode = False
            Set Found = Nothing
        Else
            With Target.Interior
                .Pattern = xlNone
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With    'target cell
        End If  'found
    End If  'in validated cells
    Application.EnableEvents = True
End Sub

This function starts at cell C11 and tests each cell in that row until it gets to a column with no
validation formula. It then goes down the rows of that column until a cell with no validation
formula is found. It returns the address of the block starting with C11 and ending with the
address of that cell.

Function FindValidationCells() As String
    Dim TestValidation  As Variant, _
        ValTest         As Boolean
    
    ValTest = True
    Set TestValidation = Range("c11")
    
    While ValTest = True
        ValTest = False
        Set TestValidation = TestValidation.Offset(0, 1)
        On Error Resume Next
        ValTest = (TestValidation.Validation.Formula1 = "=Types")
    Wend
    Set TestValidation = TestValidation.Offset(0, -1)

    Err.Clear
    ValTest = True
    
    While ValTest = True
        ValTest = False
        TestValidation.Select
        Set TestValidation = TestValidation.Offset(1)
        On Error Resume Next
        ValTest = (TestValidation.Validation.Formula1 = "=Types")
    Wend
    Err.Clear
    Set TestValidation = TestValidation.Offset(-1)
    Set TestValidation = Range("C11", TestValidation.Address(0, 0))
    FindValidationCells = TestValidation.Address(0, 0)
    Set TestValidation = Nothing
End Function

[plans]

Thanks for the response.
This new modification works the same way as before, but actually I figured a "workaround".
When I insert a column, I am getting a Run-time error' 13': Type mismatch, so what I do, I save the file, exit it and reopen.
The newly opened file works fine.
Thanks

[protonLeah]

Sorry, I forgot to test that only one cell is selected. Try:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count >1 then Exit Sub

    Dim Found As Variant, _
        ValAddress  As String

'branch to Function FindValidationCells to get the address of the block of cells
'with data validation starting at cell Cll

        ValAddress = FindValidationCells
    
    If Not Intersect(Target, Range(ValAddress)) Is Nothing Then
        Application.EnableEvents = False
        Set Found = Range("types").Find(Target.Value)
        
        If Not Found Is Nothing Then
            Found.Copy
            Target.PasteSpecial _
                Paste:=xlPasteFormats
            Application.CutCopyMode = False
            Set Found = Nothing
        Else
            With Target.Interior
                .Pattern = xlNone
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With    'target cell
        End If  'found
    End If  'in validated cells
    Application.EnableEvents = True
End Sub

[YasserKhalil]

@protonLeah
I tried to send you private message but I encountered an error ("protonLeah has chosen not to receive private messages or may not be allowed to receive private messages. Therefore you may not send your message to him/her.")

I don't get what red rep
I remember I gave you rep points as I liked your code and the solution presented
That's all
I'm admired of your way of thinking in fact

[plans]

protonLeah,
You just did it again!
Thanks