User defined function & formatting
 

Jonas,

No you cannot modify format from within a function, simply return a value.

Why is CF not sufficient. Is it because it only has 3 conditions? If so, you
could use event code liek this

Private Sub Worksheet_Change(By Target As Range)
Application.EnableEvents = False
On Error GoTo ws_exit:
If (Not Intersect(Target, Range("colours")) Is Nothing) Then
Select Case Target.Value
Case "a": Target.Interior.ColorIndex = 3
Case "b": Target.Interior.ColorIndex = 4
Case "c": Target.Interior.ColorIndex = 34
Case "d": Target.Interior.ColorIndex = 35
etc.
End Select
End If

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips

"Jonas Caspersson" wrote in message
...
Hi, i have a Excel question.

Is it possible to from within a user defined function (VBA) modify the
cells
properties, like .interior.color?

This would help since the built-in function 'Conditional formatting' is of
no help for my specific demands.

What i want is to let the user select a function called for instance
'FormatColor' from the insert function dialog box, and then choose an
refernence cell which contains the formats he wants to use.

Exampel

within VBA module:

Function FormatCell (Ref as Range)
Application.ActiveCell.Interior.Color = Ref.Interior.Color
End Function

The user then invokes the funktion via 'insert function - user defined
funtion - FormaCell'
- Picks the reference cell of chiose.

This does not work - I've tried... :o(
And by the way, Excel hangs!

(i've tried this with Excel XP)

Help, please

/Jonas Caspersson

User defined function & formatting
 

Bob,
Thank's I was beginning to doubt my VBA skills... god forgive ;o)

The reason why CF doesn't do the job is that the formated condition is'nt
linked to the cell.value it self, what i mean is if you apply a lot of CF's
and then do a 'sort' the CF is applied to the absolute cells
ie. if I applied CF to Cell(1,1) and the do a 'sort' the value of Cell(1,1)
ends up at Cell(20,1) but the CF stays at Cell(1,1) and i want the CF to go
along with the original cell.
Did you get this?

But thanks any how

"Bob Phillips" skrev i meddelandet
...
Jonas,

No you cannot modify format from within a function, simply return a value.

Why is CF not sufficient. Is it because it only has 3 conditions? If so,
you
could use event code liek this

Private Sub Worksheet_Change(By Target As Range)
Application.EnableEvents = False
On Error GoTo ws_exit:
If (Not Intersect(Target, Range("colours")) Is Nothing) Then
Select Case Target.Value
Case "a": Target.Interior.ColorIndex = 3
Case "b": Target.Interior.ColorIndex = 4
Case "c": Target.Interior.ColorIndex = 34
Case "d": Target.Interior.ColorIndex = 35
etc.
End Select
End If

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips

"Jonas Caspersson" wrote in message
...
Hi, i have a Excel question.

Is it possible to from within a user defined function (VBA) modify the
cells
properties, like .interior.color?

This would help since the built-in function 'Conditional formatting' is
of
no help for my specific demands.

What i want is to let the user select a function called for instance
'FormatColor' from the insert function dialog box, and then choose an
refernence cell which contains the formats he wants to use.

Exampel

within VBA module:

Function FormatCell (Ref as Range)
Application.ActiveCell.Interior.Color = Ref.Interior.Color
End Function

The user then invokes the funktion via 'insert function - user defined
funtion - FormaCell'
- Picks the reference cell of chiose.

This does not work - I've tried... :o(
And by the way, Excel hangs!

(i've tried this with Excel XP)

Help, please

/Jonas Caspersson

User defined function & formatting
 

Jonas,

Do you mean that cell 1 has CF but cell 20p doesn't so if you sort them and
1 ends up at 20 you lose the CF?

If so, why not apply the dame CF to 1 thru 20?

--

HTH

Bob Phillips

"Jonas Caspersson" wrote in message
...
Bob,
Thank's I was beginning to doubt my VBA skills... god forgive ;o)

The reason why CF doesn't do the job is that the formated condition is'nt
linked to the cell.value it self, what i mean is if you apply a lot of
CF's
and then do a 'sort' the CF is applied to the absolute cells
ie. if I applied CF to Cell(1,1) and the do a 'sort' the value of
Cell(1,1)
ends up at Cell(20,1) but the CF stays at Cell(1,1) and i want the CF to
go
along with the original cell.
Did you get this?

But thanks any how

"Bob Phillips" skrev i meddelandet
...
Jonas,

No you cannot modify format from within a function, simply return a
value.

Why is CF not sufficient. Is it because it only has 3 conditions? If so,
you
could use event code liek this

Private Sub Worksheet_Change(By Target As Range)
Application.EnableEvents = False
On Error GoTo ws_exit:
If (Not Intersect(Target, Range("colours")) Is Nothing) Then
Select Case Target.Value
Case "a": Target.Interior.ColorIndex = 3
Case "b": Target.Interior.ColorIndex = 4
Case "c": Target.Interior.ColorIndex = 34
Case "d": Target.Interior.ColorIndex = 35
etc.
End Select
End If

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips

"Jonas Caspersson" wrote in message
...
Hi, i have a Excel question.

Is it possible to from within a user defined function (VBA) modify the
cells
properties, like .interior.color?

This would help since the built-in function 'Conditional formatting'
is
of
no help for my specific demands.

What i want is to let the user select a function called for instance
'FormatColor' from the insert function dialog box, and then choose an
refernence cell which contains the formats he wants to use.

Exampel

within VBA module:

Function FormatCell (Ref as Range)
Application.ActiveCell.Interior.Color = Ref.Interior.Color
End Function

The user then invokes the funktion via 'insert function - user
defined
funtion - FormaCell'
- Picks the reference cell of chiose.

This does not work - I've tried... :o(
And by the way, Excel hangs!

(i've tried this with Excel XP)

Help, please

/Jonas Caspersson

User defined function & formatting
 

I'ts even worse...
If I would use CF I had to apply different CF's to cell 1 to ~100
different....
Then apply 'sort' in differnt ways.

This is hard to get, I know, but the Excel application I'm reffering to has
a *LOT* of data that has to be presented with different coloring to get an
overview, and applying CF's to all the lines & cols (approx 250 lines times
50 cols) is time consuming and i'd like to do that ONCE.
To complicate things even more, the data and format keeps changing all the
time, and adding that i want to sort the lines according to my needs
constantly excluded the CF function.

But thank's, it seems as I have to write a lot of VBA code.... ;o)

"Bob Phillips" skrev i meddelandet
...
Jonas,

Do you mean that cell 1 has CF but cell 20p doesn't so if you sort them
and
1 ends up at 20 you lose the CF?

If so, why not apply the dame CF to 1 thru 20?

--

HTH

Bob Phillips

"Jonas Caspersson" wrote in message
...
Bob,
Thank's I was beginning to doubt my VBA skills... god forgive ;o)

The reason why CF doesn't do the job is that the formated condition
is'nt
linked to the cell.value it self, what i mean is if you apply a lot of
CF's
and then do a 'sort' the CF is applied to the absolute cells
ie. if I applied CF to Cell(1,1) and the do a 'sort' the value of
Cell(1,1)
ends up at Cell(20,1) but the CF stays at Cell(1,1) and i want the CF to
go
along with the original cell.
Did you get this?

But thanks any how

"Bob Phillips" skrev i meddelandet
...
Jonas,

No you cannot modify format from within a function, simply return a
value.

Why is CF not sufficient. Is it because it only has 3 conditions? If
so,
you
could use event code liek this

Private Sub Worksheet_Change(By Target As Range)
Application.EnableEvents = False
On Error GoTo ws_exit:
If (Not Intersect(Target, Range("colours")) Is Nothing) Then
Select Case Target.Value
Case "a": Target.Interior.ColorIndex = 3
Case "b": Target.Interior.ColorIndex = 4
Case "c": Target.Interior.ColorIndex = 34
Case "d": Target.Interior.ColorIndex = 35
etc.
End Select
End If

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips

"Jonas Caspersson" wrote in message
...
Hi, i have a Excel question.

Is it possible to from within a user defined function (VBA) modify
the
cells
properties, like .interior.color?

This would help since the built-in function 'Conditional formatting'
is
of
no help for my specific demands.

What i want is to let the user select a function called for instance
'FormatColor' from the insert function dialog box, and then choose
an
refernence cell which contains the formats he wants to use.

Exampel

within VBA module:

Function FormatCell (Ref as Range)
Application.ActiveCell.Interior.Color = Ref.Interior.Color
End Function

The user then invokes the funktion via 'insert function - user
defined
funtion - FormaCell'
- Picks the reference cell of chiose.

This does not work - I've tried... :o(
And by the way, Excel hangs!

(i've tried this with Excel XP)

Help, please

/Jonas Caspersson