Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Can a spreadsheet function change a cells appearance?

  Asked By: Marty    Date: Feb 21    Category: MS Office    Views: 1089
  

I only program in Excel vba occassionally so am not conversant
with all the techniques! I am interested in whether a function written
to sit in a spreadsheet cell is capable of altering the appearance of
the cell itself and the formatting of the text which the function
returns. To make this easier to grasp, say I have strings '1234' in B1
and '124' in C1. From these inputs I want =Compare(B1,C1) sitting in D1
to produce a string '1234' (where the third character is bold and in a
different colour) to indicate it exists in the first input but not the
second. I would also like to change the cell's background colour
according the the number of differences between the two inputs (8
maximum).

I know that Excel only allows a spreadsheet function to alter the cell
to which the result is returned, but have found that I can leave a
series of results in an public array for other cells to pick up. Using
this I have successfully placed a formula
=DoMatrixManipulation(A4:C25,E4:G25) in one cell and then populated
J4:L25 with a function =ReadResult(Row()-r,Column()-c,TriggerCell) to
pick up the array elements. (I do this to to prevent Excel needlessly
recalculating every cell at each new user input, as by controlling the
contents of the trigger cell, the results range is only refreshed when
it needs to be.)

This is where I suspect I could achieve more if only if I knew how!

Share: 

 

16 Answers Found

 
Answer #1    Answered By: Abbad Akhtar     Answered On: Feb 21

I think you'll be fighting Excel every step of the way to achieve this, but I've
never investigated changing cell format as a side-effect - only changing the
CONTENT of other cells, which I have encountered on a few occasions and not
bothered to pursue.

One option that leaps to mind would be to use an asynchronous subroutine to look
at your public data and reformat the cell after the function  had done its thing.
More precisely, you'd get the function to trigger the execution of the
asynchronous subroutine using a timed delay set to "now".

Call Application.OnTime(Now, "SideEffectSubroutine")

I don't think I've ever tried to run an OnTime from a cell formula function, but
I'd anticipate that it wouldn't be blocked.

 
Answer #2    Answered By: Cais Nguyen     Answered On: Feb 21

Take a look at conditional formatting. For example

http://j-walk.com/ss/excel/usertips/tip006.htm

Excel "to 007" has some really interesting extensions.

http://blogs.msdn.com/excel/

 
Answer #3    Answered By: Jaspreet Kapoor     Answered On: Feb 21

If you reread the original question, I believe you will find that the interest
is on formatting parts of a cell (individual characters) - not the whole cell.
Also that the part about changing the background colour wanted eight different
colours. Both of these are well beyond conditional formatting.

 
Answer #4    Answered By: Elaine Stevens     Answered On: Feb 21

I was aware of what he was asking, I was just making a suggestion of an
alternate approach that in the next version of Excel, that will be out later
this year, offers far more possibilities. Rather than one conditional format
per cell you can have many.

As to the multi colouring, I can only see that being done through VBA. The
actual logic on what character changes to what colour is not that hard. The
actual colour change  can be done with

ActiveCell.Characters(Start:=1, Length:=1).Font.ColorIndex = 12
ActiveCell.Characters(Start:=2, Length:=1).Font.ColorIndex = 17
ActiveCell.Characters(Start:=3, Length:=1).Font.ColorIndex = 4
ActiveCell.Characters(Start:=4, Length:=1).Font.ColorIndex = 46

 
Answer #5    Answered By: Alexis Castillo     Answered On: Feb 21

>Rather than one conditional format
per cell

er.... being a really annoying nitpicker I gues but....Three... I think

 
Answer #6    Answered By: Dot net Sachin     Answered On: Feb 21

No need to duck, I'm the mild one, just don't get on Dian's bad side. ;-)

One of the demos of Excel 2 007 I saw had twelve conditions of the sort
(if value = 1 then fill colour = red...)
So it looks like they can handle more than three conditions.

 
Answer #7    Answered By: Renee Lane     Answered On: Feb 21

You didn't point out that you were talking about future capabilities - yes,
additional conditional formatting capability will be nice, but not of much use
now.

I think you'll find that the original questioner is aware that the
multi-colouring can only be done with VBA. I certainly am. That was why he
asked about the ability to get a [vba] function  called from a cell formula to
affect other cells. He's already half-way there, and I was supplying one way of
moving the next step.

 
Answer #8    Answered By: Volney Fischer     Answered On: Feb 21

You didn't point out that you were talking about future capabilities - yes,
additional conditional formatting capability will be nice, but not of much use
now.

I think you'll find that the original questioner is aware that the
multi-colouring can only be done with VBA. I certainly am. That was why he
asked about the ability to get a [vba] function  called from a cell formula to
affect other cells. He's already half-way there, and I was supplying one way of
moving the next step.

 
Answer #9    Answered By: Sophie Campbell     Answered On: Feb 21

As a side barb... While I can offer no help with the "individual characters"
portion, the 3 condition limit with conditional formatting can possibly be
worked around via a lookup table. I've used the following technique to allow
users to change  series names & colors on a group of locked charts.

An protected lookup table contains three columns: "Index", "Series Name" &
"Color". The cells  for "Series Name" & "Color" are unlocked and the user is
instructed to change the "Series Name" to the desired text for each series name
and to manually change the cell fill color for the "Color" cell to the desired
color. When the code is executed, a Vlookup of "Index" reads/changes desired
"Series Name", and also sets the series color to match the Interior.ColorIndex
property of "Color".

If the "conditions" can be defined and assigned an "Index", the above technique
might provide an easily customizable method to conditionally format according to
an unlimited number of conditions.

 
Answer #10    Answered By: Adalwine Fischer     Answered On: Feb 21

Worksheet change  is a good candidate, and aesthetically more "nice"
than the idea of an asynchronous routine fire-off.

However (i.e. no free lunch), the worksheet change event is a bit scatter-gun,
in that it fires with a change in any cell, whether you're interested in that
one or not. So you've then got to work out whether you should do the work on
the cell. I certainly use this approach in some instances, and ignoring
unwanted cells  isn't particularly cumbersome.

 
Answer #11    Answered By: Kristin Johnston     Answered On: Feb 21

I wouldn't even call it robust. In these groups and forums, there is usually
more people involved than a person asking and a person answering a question.
There are usually a number of people with similar questions or partial
suggestions. So most questions can be considered the starting point of a
discussion. The question posed may not be answerable by an individual, but
the group may come up with enough bits and pieces for a workable solution.
So it may appear that you have started a fight, it's just a number of people
throwing out information that may help.

Depending on your time frame, the conditional formatting should be a
solution when the next version of Office is released later this year.

 
Answer #12    Answered By: Beatriz Silva     Answered On: Feb 21

I've had some "issues" with conditional formatting before when I've tried to
copy and paste using VBA. Have you any idea if that is still the case
please?

 
Answer #13    Answered By: Yvonne Watkins     Answered On: Feb 21

Do you have an example?
I may be able to ask someone to try it out on Excel 2007.

 
Answer #14    Answered By: Yvette Griffin     Answered On: Feb 21

can't find my example... I think because I fixed it :-) but I
have found a note in my code pointing me at
http://support.microsoft.com/kb/231144/en-us

 
Answer #15    Answered By: Brent Brown     Answered On: Feb 21

I don't have the time right now to play around with creating a function,
but I do have a macro which will color and concatenate two cells
together.

For your pleasure:

Sub ColorAndConcatenate()
'Concatenates two cells  together and returns them
'Formatted. First portion will return in Arial Black 12pts
'While second portion will return in Times New Roman 36pts, Red
Dim intCharFirst As Integer
Dim intCharSec As Integer
Dim strFirst As String
Dim strSecond As String

Do While ActiveCell.Value <> ""
strFirst = ActiveCell.Value
intCharFirst = Len(strFirst)

'Move to next cell in list
ActiveCell.Offset(1, 0).Range("A1").Select
strSecond = ActiveCell.Value
intCharSec = Len(strSecond)

'Move to next cell in list
ActiveCell.Offset(1, 0).Range("A1").Select

'Concatenate cells together
ActiveCell.Value = strFirst & strSecond

'Format the New Cell
'First Part
ActiveCell.FormulaR1C1 = strFirst & strSecond
With ActiveCell.Characters(Start:=1, _
Length:=intCharFirst).Font
.Name = "Arial Black"
.FontStyle = "Regular"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

'Second Part
With ActiveCell.Characters(Start:=intCharFirst + 1, _
Length:=intCharSec).Font
.Name = "Times New Roman"
.FontStyle = "Regular"
.Size = 36
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 3
End With
'Reset Variables
strFirst = ""
strSecond = ""
intCharFirst = 0
intCharSec = 0

'Move to next cell in list
ActiveCell.Offset(1, 0).Range("A1").Select

Loop

End Sub

 
Answer #16    Answered By: Cambria Lopez     Answered On: Feb 21

Could you give a little more info on the sort of troubles you've had? I often
set new values into cells  from vba  without compromising their formatting
(including conditional formatting).

If you copy/paste over a cell (from the keyboard as well as from VBA), the
target cell will of course lose its original formatting. But if you use
paste/special and avoid pasting the formatting, you'll allow the target cell's
format to remain intact.

 
Didn't find what you were looking for? Find more on Can a spreadsheet function change a cells appearance? Or get search suggestion and latest updates.




Tagged: