Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Jo
 
Posts: n/a
Default 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!!
  #2   Report Post  
Frank Kabel
 
Posts: n/a
Default

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


  #3   Report Post  
Jo
 
Posts: n/a
Default

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



  #4   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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

  #5   Report Post  
Jo
 
Posts: n/a
Default

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




  #6   Report Post  
JulieD
 
Posts: n/a
Default

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



  #7   Report Post  
Jo
 
Posts: n/a
Default

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




  #8   Report Post  
JulieD
 
Posts: n/a
Default

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






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
I want to format a cell based on an adjacent cells value Cumbo Excel Discussion (Misc queries) 1 December 13th 04 12:52 PM
Modify Row & Cell Contents based upon Cells Values bpat1434 Excel Worksheet Functions 0 November 7th 04 04:31 PM
Modify Row & Cell Contents based upon Cells Values bpat1434 Excel Worksheet Functions 1 November 7th 04 01:43 PM
Modify Row & Cell Contents based upon Cells Values bpat1434 Excel Worksheet Functions 1 November 7th 04 04:12 AM
Modify Row & Cell Contents based upon Cells Values bpat1434 Excel Worksheet Functions 1 November 6th 04 06:17 PM


All times are GMT +1. The time now is 09:27 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"