Put an autoshape in a cell based on another cells content
 

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!!

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Jo" schrieb im Newsbeitrag
...
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!!

Hi Frank

Yes this would feasible for me.
Hope you can help

Thanks!

"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Jo" schrieb im Newsbeitrag
...
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!!

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 FormatConditional 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
On the Font tab, choose the Orange colour
Click OK, click OK


Jo wrote:
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!!


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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

Jo

"Debra Dalgleish" wrote:

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 FormatConditional 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
On the Font tab, choose the Orange colour
Click OK, click OK


Jo wrote:
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!!


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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:
Select cell A1
Type the formula: =IF(B1="","",l)
Format the cell with Wingding font

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" wrote in message
...
Thanks Debra! That works superbly! But what if there are more than 4
colours
required?

Jo

Thank you Julie!! Thats brilliant!

Jo

"JulieD" wrote:

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:
Select cell A1
Type the formula: =IF(B1="","",l)
Format the cell with Wingding font

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" wrote in message
...
Thanks Debra! That works superbly! But what if there are more than 4
colours
required?

Jo

Hi Jo

glad to assist & thanks for the feedback

Cheers
JulieD

"Jo" wrote in message
...
Thank you Julie!! Thats brilliant!

Jo

"JulieD" wrote:

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:
Select cell A1
Type the formula: =IF(B1="","",l)
Format the cell with Wingding font

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" wrote in message
...
Thanks Debra! That works superbly! But what if there are more than 4
colours
required?

Jo