Auto Change Pictures, Colours and Fonts with Formula or VBA

[Bazman1981]

I have an excel workbook that has multiple sheets. There are a number of headings and boxes that I have formatted the same, for example a black background with white font. The sheets are all locked so that people cannot alter the formatting etc.

I have a "data sheet" which I also lock and hide, which provides me my dropdown lists, data and changeable information etc so that I can edit 1 sheet and all of the changes feed through to the rest of the workbook without me having to manually unlock each sheet and change it.

What I would like to be able to do is change the background colour & font colour by just opening my data sheet and typing in a letter. So I have managed to do it, by using Conditional Formatting and setting the formula so that if I type "B" into my cell in the data sheet, the background on all of the affected cells will change to black. I have done the same for font. However, this will take ages to enter the rule for up to say 10 colours and then 10 fonts. Is there a quick way I can do this where 1 formula will enable me to change font and background based on selecting the colour in each cell of my data sheet? i.e. Cell A1 says Black, Red, Blue etc for the background and Cell A2 says the same for the font.

My second question, is can I do the same thing with a picture? i.e. I have a picture at the top of each sheet (it is a company logo) but can I set it so that I can change the company logo or picture on my data sheet and that same logo/picture changes on every locked page?

Thanks in advance for your help, hope my questions are clear

 

[Worf]

Place all the pictures on the sheets, the only visible one will be the name chosen at cell A3:

' data sheet module
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cnt%, i%, ads(), j%, clr&, v, f&
If Not Intersect(Target, [a1:a3]) Is Nothing Then
'    cnt = ActiveWorkbook.Sheets.Count                              ' all sheets
    cnt = 2                                                         ' testing
    ReDim ads(1 To cnt)
    ads(1) = "a1:d5,e2:f7"                                          ' affected cells, first sheet
    ads(2) = "g1:h9,k1:n7,p1:v6"                                    ' affected cells, second sheet
    For i = 1 To cnt                                                ' loop the sheets
        If Me.Name <> Sheets(i).Name Then                           ' not the data sheet
            v = Split(ads(i), ",")
            Sheets(i).Unprotect "pass"
            If Target = [a1] Then                                   ' background
                Select Case UCase(Target)
                    Case Is = "R":                 clr = 255
                    Case Is = "S":                 clr = 100
                End Select
                For j = LBound(v) To UBound(v)
                    Sheets(i).Range(v(j)).Interior.Color = clr
                Next
            End If
            If Target = [a2] Then                                   ' font
                Select Case UCase(Target)
                    Case Is = "Y":                        f = 65535
                    Case Is = "G":                        f = vbGreen
                End Select
                For j = LBound(v) To UBound(v)
                    Sheets(i).Range(v(j)).Font.Color = f
                Next
            End If
            If Target = [a3] Then                                   ' picture
                For j = 1 To Sheets(i).Shapes.Count
                    Sheets(i).Shapes(j).Visible = False
                    If Sheets(i).Shapes(j).Name = [a3] Then Sheets(i).Shapes(j).Visible = 1
                Next
            End If
            Sheets(i).Protect "pass"
        End If
    Next
End If
End Sub

 

[Bazman1981]

Thank-you