Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default Display a part of a cell in green (or red)...

.... according to the value of a cell hosted in another worksheet...

Hi,

My cell A4 of the worksheet "Dashboard" is:
="Evolution of " & TEXT('Global Data'!C39;"#%") & " from previous month"

It displays:
Evolution of 39% from previous month

I would like that if 'Global Data'!C39 = 0, "& TEXT('Global
Data'!C39;"#%")" is written in green, else it is written in red

I think the only solution is to add code ... However I do not know a lot in
VB...

Can someone help?

Thanks
--
Anne P
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,441
Default Display a part of a cell in green (or red)...

Anne,

Name cell A4 of "Dash Board" ColorEvolution, and name cell C39 of "Global Data" DSource. (Using
names instead of cell addresses will ensure that the code works even if you or your user inserts
rows or columns somewhere.)

Copy the code below, right-click the sheet tab of "DashBoard" and select "View Code" then paste the
code into the window that appears.

Then, in another cell on Dash Board, type
=DSource
Having that link will force a calculation of Dash Board any time there is a change in DSource, which
will then fire the calculate event below.

In the line

.ColorIndex = IIf(myV.Value = 0, 50, 3)

50 is the green and 3 is the red, so if you want to change the specific color, do it there.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Dim myFR As Range
Dim myV As Range

Set myV = Worksheets("Global Data").Range("Dsource")
Set myFR = Range("ColorEvolution")

With myFR
.Value = "Evolution of " & Format(myV.Value, "#%") & " from previous month"
With .Characters(Start:=14, Length:=Len(Format(myV.Value, "#%"))).Font
.ColorIndex = IIf(myV.Value = 0, 50, 3)
End With
End With
Application.EnableEvents = True
End Sub


"Anne P" wrote in message
...
... according to the value of a cell hosted in another worksheet...

Hi,

My cell A4 of the worksheet "Dashboard" is:
="Evolution of " & TEXT('Global Data'!C39;"#%") & " from previous month"

It displays:
Evolution of 39% from previous month

I would like that if 'Global Data'!C39 = 0, "& TEXT('Global
Data'!C39;"#%")" is written in green, else it is written in red

I think the only solution is to add code ... However I do not know a lot in
VB...

Can someone help?

Thanks
--
Anne P



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default Display a part of a cell in green (or red)...

Hello Bernie,

Thank you for your clear answer, I have been able to integrate it in my
worksheet, + it works. :-)

Now, I am trying to replicate the code so that this appears for several data
in the same worksheet, but it does not apply... The debug feature do not find
any error though.

My code:

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Dim myFR As Range
Dim myV As Range

Set myV = Worksheets("Global Data").Range("Dsource")
Set myFR = Range("ColorEvolution")

With myFR
.Value = "Evolution of " & Format(myV.Value, "#%") & " from previous month"
With .Characters(Start:=14, Length:=Len(Format(myV.Value, "#%"))).Font
.ColorIndex = IIf(myV.Value = 0, 50, 3)
End With
End With
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Calculate2()
Application.EnableEvents = False
Dim myFR2 As Range
Dim myV2 As Range

Set myV2 = Worksheets("Global Data").Range("RateVisitors")
Set myFR2 = Range("EvolutionVisitors")

With myFR2
.Value = "Evolution of " & Format(myV2.Value, "#%") & " from previous
month"
With .Characters(Start:=14, Length:=Len(Format(myV2.Value, "#%"))).Font
.ColorIndex = IIf(myV2.Value = 0, 50, 3)
End With
End With
Application.EnableEvents = True
End Sub

Could you please let me know what is wrong with this code??? I would need to
replicate it at least 5 times on the worksheet.

Thank you for your precious help on this!

Anne

--
Anne P


"Bernie Deitrick" wrote:

Anne,

Name cell A4 of "Dash Board" ColorEvolution, and name cell C39 of "Global Data" DSource. (Using
names instead of cell addresses will ensure that the code works even if you or your user inserts
rows or columns somewhere.)

Copy the code below, right-click the sheet tab of "DashBoard" and select "View Code" then paste the
code into the window that appears.

Then, in another cell on Dash Board, type
=DSource
Having that link will force a calculation of Dash Board any time there is a change in DSource, which
will then fire the calculate event below.

In the line

.ColorIndex = IIf(myV.Value = 0, 50, 3)

50 is the green and 3 is the red, so if you want to change the specific color, do it there.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Dim myFR As Range
Dim myV As Range

Set myV = Worksheets("Global Data").Range("Dsource")
Set myFR = Range("ColorEvolution")

With myFR
.Value = "Evolution of " & Format(myV.Value, "#%") & " from previous month"
With .Characters(Start:=14, Length:=Len(Format(myV.Value, "#%"))).Font
.ColorIndex = IIf(myV.Value = 0, 50, 3)
End With
End With
Application.EnableEvents = True
End Sub


"Anne P" wrote in message
...
... according to the value of a cell hosted in another worksheet...

Hi,

My cell A4 of the worksheet "Dashboard" is:
="Evolution of " & TEXT('Global Data'!C39;"#%") & " from previous month"

It displays:
Evolution of 39% from previous month

I would like that if 'Global Data'!C39 = 0, "& TEXT('Global
Data'!C39;"#%")" is written in green, else it is written in red

I think the only solution is to add code ... However I do not know a lot in
VB...

Can someone help?

Thanks
--
Anne P




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,441
Default Display a part of a cell in green (or red)...

Anne,

You only get one event, so you need to put all your code together - you could use somthing like the
first procedure, where you reuse the variables.

But a better approach would be to improve your naming convention to allow you to loop through the
cells without adding code sections for each pair, for an unlimited number of pairs. So, name the
source cells DSource1, DSource2, etc. and the evolving cells ColorEvolution1, ColorEvolution2, etc.,
then use the second procedure. Just make sure that you have consecutive names for the ranges - the
code starts at DSource1, and when it doesn't find DSourceN, it quits.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Dim myFR As Range
Dim myV As Range

Set myV = Worksheets("Global Data").Range("Dsource")
Set myFR = Range("ColorEvolution")

With myFR
.Value = "Evolution of " & Format(myV.Value, "#%") & " from previous month"
With .Characters(Start:=14, Length:=Len(Format(myV.Value, "#%"))).Font
.ColorIndex = IIf(myV.Value = 0, 50, 3)
End With
End With

'Re-use the variables and do the same thing again:
Set myV = Worksheets("Global Data").Range("RateVisitors")
Set myFR = Range("EvolutionVisitors")

With myFR
.Value = "Evolution of " & Format(myV.Value, "#%") & " from previous Month """
With .Characters(Start:=14, Length:=Len(Format(myV.Value, "#%"))).Font
.ColorIndex = IIf(myV.Value = 0, 50, 3)
End With
End With

Application.EnableEvents = True
End Sub

'Use this version if you change your naming convention
Private Sub Worksheet_Calculate()
Dim myFR As Range
Dim myV As Range
Dim i As Integer

i = 0

On Error GoTo Finished
Application.EnableEvents = False

TryAgain:

i = i + 1

Set myV = Worksheets("Global Data").Range("Dsource" & i)
Set myFR = Range("ColorEvolution" & i)

With myFR
.Value = "Evolution of " & Format(myV.Value, "#%") & " from previous month"
With .Characters(Start:=14, Length:=Len(Format(myV.Value, "#%"))).Font
.ColorIndex = IIf(myV.Value = 0, 50, 3)
End With
End With

GoTo TryAgain

Finished:

Application.EnableEvents = True
End Sub



"Anne P" wrote in message
...
Hello Bernie,

Thank you for your clear answer, I have been able to integrate it in my
worksheet, + it works. :-)

Now, I am trying to replicate the code so that this appears for several data
in the same worksheet, but it does not apply... The debug feature do not find
any error though.

My code:

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Dim myFR As Range
Dim myV As Range

Set myV = Worksheets("Global Data").Range("Dsource")
Set myFR = Range("ColorEvolution")

With myFR
.Value = "Evolution of " & Format(myV.Value, "#%") & " from previous month"
With .Characters(Start:=14, Length:=Len(Format(myV.Value, "#%"))).Font
.ColorIndex = IIf(myV.Value = 0, 50, 3)
End With
End With
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Calculate2()
Application.EnableEvents = False
Dim myFR2 As Range
Dim myV2 As Range

Set myV2 = Worksheets("Global Data").Range("RateVisitors")
Set myFR2 = Range("EvolutionVisitors")

With myFR2
.Value = "Evolution of " & Format(myV2.Value, "#%") & " from previous
month"
With .Characters(Start:=14, Length:=Len(Format(myV2.Value, "#%"))).Font
.ColorIndex = IIf(myV2.Value = 0, 50, 3)
End With
End With
Application.EnableEvents = True
End Sub

Could you please let me know what is wrong with this code??? I would need to
replicate it at least 5 times on the worksheet.

Thank you for your precious help on this!

Anne

--
Anne P


"Bernie Deitrick" wrote:

Anne,

Name cell A4 of "Dash Board" ColorEvolution, and name cell C39 of "Global Data" DSource.
(Using
names instead of cell addresses will ensure that the code works even if you or your user inserts
rows or columns somewhere.)

Copy the code below, right-click the sheet tab of "DashBoard" and select "View Code" then paste
the
code into the window that appears.

Then, in another cell on Dash Board, type
=DSource
Having that link will force a calculation of Dash Board any time there is a change in DSource,
which
will then fire the calculate event below.

In the line

.ColorIndex = IIf(myV.Value = 0, 50, 3)

50 is the green and 3 is the red, so if you want to change the specific color, do it there.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Dim myFR As Range
Dim myV As Range

Set myV = Worksheets("Global Data").Range("Dsource")
Set myFR = Range("ColorEvolution")

With myFR
.Value = "Evolution of " & Format(myV.Value, "#%") & " from previous month"
With .Characters(Start:=14, Length:=Len(Format(myV.Value, "#%"))).Font
.ColorIndex = IIf(myV.Value = 0, 50, 3)
End With
End With
Application.EnableEvents = True
End Sub


"Anne P" wrote in message
...
... according to the value of a cell hosted in another worksheet...

Hi,

My cell A4 of the worksheet "Dashboard" is:
="Evolution of " & TEXT('Global Data'!C39;"#%") & " from previous month"

It displays:
Evolution of 39% from previous month

I would like that if 'Global Data'!C39 = 0, "& TEXT('Global
Data'!C39;"#%")" is written in green, else it is written in red

I think the only solution is to add code ... However I do not know a lot in
VB...

Can someone help?

Thanks
--
Anne P






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
display part of the cell content linda Excel Discussion (Misc queries) 4 August 28th 07 03:02 AM
how to colour a cell green if another cell is filled with text terence Excel Discussion (Misc queries) 3 March 11th 07 05:38 PM
Display contents of cell in another cell as part of text string? [email protected] New Users to Excel 3 July 8th 06 07:44 PM
Simple Question, display only part of a date in Number format DB Explorer Excel Worksheet Functions 6 March 17th 06 12:47 PM
Display Part of the cell value ramana Excel Worksheet Functions 6 November 17th 05 10:34 AM


All times are GMT +1. The time now is 12:07 PM.

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"