Put an autoshape in a cell based on another cells content

[Jo]

Hi

Hope you can help. I want to have a red circle to appear in say A1 when B1
has VH in it. The values in B1 will change and I would want different colour
circles putting in dependent on the content of cell B1.

Thanks in advance!!

 

[Frank Kabel]

Hi
this would require VBA (using an event procedure). Would this be
feasible for you?

 

[Jo]

Hi Frank

Yes this would feasible for me.
Hope you can help

Thanks!

 

[Debra Dalgleish]

If there are only four colours required, you could use conditional
formatting to create the coloured circles. For example, if choices in
cell B1 are VH or BC or XY :

Select cell A1
Type the formula: =IF(B1="","",l)
Format the cell with Wingding font
Choose Format>Conditional Formatting
From the first dropdown, choose Formula Is
In the text box, type: =B1="VH"
Click the Format button
On the Font tab, choose the Red colour
Click OK, click Add

For condition 2, from the first dropdown, choose Formula Is
In the text box, type: =B1="BC"
Click the Format button
On the Font tab, choose the Green colour
Click OK, click Add

For condition 3, from the first dropdown, choose Formula Is
In the text box, type: =B1="XY"
Click the Format button

 

[Jo]

Thanks Debra! That works superbly! But what if there are more than 4 colours
required?

Jo

 

[JulieD]

Hi Jo

then you need to use a VBA solution, here's one way ...

first of all you still do the first few steps of Debra's reply:
then the following code pasted into the "sheet module" of the sheet will do
the conditional formatting for you - in this example i've used 9 conditions
....
right mouse click on the sheet tab and choose view / code
you should see on the top left of the VBE window your file name in bold (if
not try view / project explorer) and the sheet that you were on selected ...
that's the "sheet module" ... if
the wrong sheet is selected then just double click on the correct one
on the right you should see some white space - copy & paste the code in
there -
assuming you want the conditional formatting to work on cell A1 based on the
value in B1
'--code start
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("B1")) Is Nothing Then
With Target
Select Case .Value
Case "VH": Range("A1").Font.ColorIndex = 4
Case "BC": Range("A1").Font.ColorIndex = 3
Case "XY": Range("A1").Font.ColorIndex = 0
Case "AA": Range("A1").Font.ColorIndex = 6
Case "AB": Range("A1").Font.ColorIndex = 13
Case "AC": Range("A1").Font.ColorIndex = 46
Case "AD": Range("A1").Font.ColorIndex = 11
Case "AE": Range("A1").Font.ColorIndex = 7
Case "AF": Range("A1").Font.ColorIndex = 55
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
'---code end

Hope this helps
Cheers
JulieD

 

[Jo]

Thank you Julie!! Thats brilliant!

Jo

Hi Jo

then you need to use a VBA solution, here's one way ...

first of all you still do the first few steps of Debra's reply:

then the following code pasted into the "sheet module" of the sheet will do
the conditional formatting for you - in this example i've used 9 conditions
....
right mouse click on the sheet tab and choose view / code
you should see on the top left of the VBE window your file name in bold (if
not try view / project explorer) and the sheet that you were on selected ...
that's the "sheet module" ... if
the wrong sheet is selected then just double click on the correct one
on the right you should see some white space - copy & paste the code in
there -
assuming you want the conditional formatting to work on cell A1 based on the
value in B1
'--code start
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("B1")) Is Nothing Then
With Target
Select Case .Value
Case "VH": Range("A1").Font.ColorIndex = 4
Case "BC": Range("A1").Font.ColorIndex = 3
Case "XY": Range("A1").Font.ColorIndex = 0
Case "AA": Range("A1").Font.ColorIndex = 6
Case "AB": Range("A1").Font.ColorIndex = 13
Case "AC": Range("A1").Font.ColorIndex = 46
Case "AD": Range("A1").Font.ColorIndex = 11
Case "AE": Range("A1").Font.ColorIndex = 7
Case "AF": Range("A1").Font.ColorIndex = 55
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
'---code end

Hope this helps
Cheers
JulieD

 

[JulieD]

Hi Jo

glad to assist & thanks for the feedback

Cheers
JulieD