Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default Help with If Not Intersect

Excel 2000
With a previous question and your help I'm up and running
with the code below. But I need a last bit of help please
to find away round a glitch..
Users enter data into the sheet within a range of C5:H33.
For ease they sometime copy and paste part of a row into
the next row but sometime paste it into the wrong cell.
The result is the red/bold format if present in the F5:F33
(from the code below) is transfered to another cell. How
can I ensure that all other cells other than the F5:F33
range remains black and normal when data is enterd either
directly or copied from a cell with red/bold format?

Private Sub workbook_sheetchange(ByVal sh As Object, ByVal
Target As Range)
On Error Resume Next

If Not Intersect(Target, Range("f5:f33")) Is Nothing Then
If Target.Value = 0.5 Then
Target.Font.ColorIndex = 3: Target.Font.FontStyle = "bold"
Else
Target.Font.ColorIndex = 1: Target.Font.FontStyle
= "normal"
End If
End If
End Sub

TIA Derek..
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 620
Default Help with If Not Intersect

Derek,

Try this, It's a bit more intensive, but works

Private Sub workbook_sheetchange(ByVal sh As Object, ByVal Target As Range)
Dim cell As Range
On Error Resume Next

For Each cell In Target
If Not Intersect(cell, Range("f5:f33")) Is Nothing Then
If cell.Value = 0.5 Then
cell.Font.ColorIndex = 3
cell.Font.FontStyle = "bold"
Else
cell.Font.ColorIndex = 1
cell.Font.FontStyle = "normal"
End If
End If
Next cell
End Sub


--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"derek" wrote in message
...
Excel 2000
With a previous question and your help I'm up and running
with the code below. But I need a last bit of help please
to find away round a glitch..
Users enter data into the sheet within a range of C5:H33.
For ease they sometime copy and paste part of a row into
the next row but sometime paste it into the wrong cell.
The result is the red/bold format if present in the F5:F33
(from the code below) is transfered to another cell. How
can I ensure that all other cells other than the F5:F33
range remains black and normal when data is enterd either
directly or copied from a cell with red/bold format?

Private Sub workbook_sheetchange(ByVal sh As Object, ByVal
Target As Range)
On Error Resume Next

If Not Intersect(Target, Range("f5:f33")) Is Nothing Then
If Target.Value = 0.5 Then
Target.Font.ColorIndex = 3: Target.Font.FontStyle = "bold"
Else
Target.Font.ColorIndex = 1: Target.Font.FontStyle
= "normal"
End If
End If
End Sub

TIA Derek..



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Help with If Not Intersect

Bob thanks for the quick response, but I'm still stuck,
I'm sure its me not explaing it right. You code works
fine but if I copy a cell from the range F5:F33 that has a
value of 0.6 (Its now red and bold because of your code)
that format is applied when pasted to say cell C5. I need
it to be black/normal. Users of my sheet are
occaisionally accidently copying and pasting a cell from
the F5:F33 range elsewhere in the sheet and what should be
black is formatted red.

TIA derek..
-----Original Message-----
Derek,

Try this, It's a bit more intensive, but works

Private Sub workbook_sheetchange(ByVal sh As Object,

ByVal Target As Range)
Dim cell As Range
On Error Resume Next

For Each cell In Target
If Not Intersect(cell, Range("f5:f33")) Is

Nothing Then
If cell.Value = 0.5 Then
cell.Font.ColorIndex = 3
cell.Font.FontStyle = "bold"
Else
cell.Font.ColorIndex = 1
cell.Font.FontStyle = "normal"
End If
End If
Next cell
End Sub


--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"derek" wrote in message
...
Excel 2000
With a previous question and your help I'm up and

running
with the code below. But I need a last bit of help

please
to find away round a glitch..
Users enter data into the sheet within a range of

C5:H33.
For ease they sometime copy and paste part of a row into
the next row but sometime paste it into the wrong cell.
The result is the red/bold format if present in the

F5:F33
(from the code below) is transfered to another cell.

How
can I ensure that all other cells other than the F5:F33
range remains black and normal when data is enterd

either
directly or copied from a cell with red/bold format?

Private Sub workbook_sheetchange(ByVal sh As Object,

ByVal
Target As Range)
On Error Resume Next

If Not Intersect(Target, Range("f5:f33")) Is Nothing

Then
If Target.Value = 0.5 Then
Target.Font.ColorIndex = 3: Target.Font.FontStyle

= "bold"
Else
Target.Font.ColorIndex = 1: Target.Font.FontStyle
= "normal"
End If
End If
End Sub

TIA Derek..



.

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default Help with If Not Intersect

alter it to this:

rivate Sub workbook_sheetchange(ByVal sh As Object, ByVal Target As Range)
Dim cell As Range
On Error Resume Next

For Each cell In Target
If Not Intersect(cell, Range("f5:f33")) Is Nothing Then
If cell.Value = 0.5 Then
cell.Font.ColorIndex = 3
cell.Font.FontStyle = "bold"
Else
cell.Font.ColorIndex = xlAutomatic
cell.Font.FontStyle = "normal"
End If
Else
cell.Font.ColorIndex = xlAutomatic
cell.Font.FontStyle = "normal"

End If
Next cell
End Sub


Regards,
Tom Ogilvy

"derek" wrote in message
...
Bob thanks for the quick response, but I'm still stuck,
I'm sure its me not explaing it right. You code works
fine but if I copy a cell from the range F5:F33 that has a
value of 0.6 (Its now red and bold because of your code)
that format is applied when pasted to say cell C5. I need
it to be black/normal. Users of my sheet are
occaisionally accidently copying and pasting a cell from
the F5:F33 range elsewhere in the sheet and what should be
black is formatted red.

TIA derek..
-----Original Message-----
Derek,

Try this, It's a bit more intensive, but works

Private Sub workbook_sheetchange(ByVal sh As Object,

ByVal Target As Range)
Dim cell As Range
On Error Resume Next

For Each cell In Target
If Not Intersect(cell, Range("f5:f33")) Is

Nothing Then
If cell.Value = 0.5 Then
cell.Font.ColorIndex = 3
cell.Font.FontStyle = "bold"
Else
cell.Font.ColorIndex = 1
cell.Font.FontStyle = "normal"
End If
End If
Next cell
End Sub


--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"derek" wrote in message
...
Excel 2000
With a previous question and your help I'm up and

running
with the code below. But I need a last bit of help

please
to find away round a glitch..
Users enter data into the sheet within a range of

C5:H33.
For ease they sometime copy and paste part of a row into
the next row but sometime paste it into the wrong cell.
The result is the red/bold format if present in the

F5:F33
(from the code below) is transfered to another cell.

How
can I ensure that all other cells other than the F5:F33
range remains black and normal when data is enterd

either
directly or copied from a cell with red/bold format?

Private Sub workbook_sheetchange(ByVal sh As Object,

ByVal
Target As Range)
On Error Resume Next

If Not Intersect(Target, Range("f5:f33")) Is Nothing

Then
If Target.Value = 0.5 Then
Target.Font.ColorIndex = 3: Target.Font.FontStyle

= "bold"
Else
Target.Font.ColorIndex = 1: Target.Font.FontStyle
= "normal"
End If
End If
End Sub

TIA Derek..



.



  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 109
Default Help with If Not Intersect

It is really impossible to outguess your users, they will win at every
attempt <grin.

I would add code to re-format the range each time, or just before the
workbook is closed.

Regards
BrianB
================================================== ==



"derek" wrote in message ...
Excel 2000
With a previous question and your help I'm up and running
with the code below. But I need a last bit of help please
to find away round a glitch..
Users enter data into the sheet within a range of C5:H33.
For ease they sometime copy and paste part of a row into
the next row but sometime paste it into the wrong cell.
The result is the red/bold format if present in the F5:F33
(from the code below) is transfered to another cell. How
can I ensure that all other cells other than the F5:F33
range remains black and normal when data is enterd either
directly or copied from a cell with red/bold format?

Private Sub workbook_sheetchange(ByVal sh As Object, ByVal
Target As Range)
On Error Resume Next

If Not Intersect(Target, Range("f5:f33")) Is Nothing Then
If Target.Value = 0.5 Then
Target.Font.ColorIndex = 3: Target.Font.FontStyle = "bold"
Else
Target.Font.ColorIndex = 1: Target.Font.FontStyle
= "normal"
End If
End If
End Sub

TIA Derek..



  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 111
Default Help with If Not Intersect

Derek

Here´s another approach.

The below function returns the complementary range
of two ranges. If you have two ranges A an B, then
A's complement in regard to B is all the cells in A, which are
not in B. This function may come up handy in several situations.

In your example SuperRange is C5:H33 and SubRange is F5:F33,
so the complementrange is C5:E33 + G5:H33, the range you want
to keep its formatting.

This setup will do the job:

Place the function in a general module.

Function ComplementaryRange(SuperRange As Range, _
SubRange As Range) As Range
'Leo Heuser, 11 July 2003
Dim ComplementRange As Range
Dim Counter As Long
Dim PartRange(1 To 4) As Range

On Error Resume Next

Set SubRange = Intersect(SuperRange, SubRange)

With SuperRange
Set PartRange(1) = Cells(.Row, .Column).Resize(SubRange.Row - _
.Row, .Columns.Count)

Set PartRange(2) = Cells(SubRange.Row, .Column). _
Resize(SubRange.Rows.Count, SubRange.Column - .Column)

Set PartRange(3) = Cells(SubRange.Row, SubRange.Column + _
SubRange.Columns.Count). _
Resize(SubRange.Rows.Count, .Columns.Count - (SubRange.Column -
_
.Column + SubRange.Columns.Count))

Set PartRange(4) = Cells(SubRange.Row + SubRange.Rows.Count,
..Column). _
Resize(.Rows.Count - (SubRange.Row - .Row +
SubRange.Rows.Count), _
.Columns.Count)
End With

For Counter = 1 To 4
If Not PartRange(Counter) Is Nothing Then
If ComplementRange Is Nothing Then
Set ComplementRange = PartRange(Counter)
Else
Set ComplementRange = Union(ComplementRange, _
PartRange(Counter))
End If
End If
Next Counter

Set ComplementaryRange = ComplementRange

On Error Goto 0
End Function


Make the following adjustments to your code:

Private Sub workbook_sheetchange(ByVal sh As Object, ByVal Target As Range)
Dim ComplementRange As Range

On Error Resume Next

If Not Intersect(Target, Range("f5:f33")) Is Nothing Then
If Target.Value = 0.5 Then
Target.Font.ColorIndex = 3: Target.Font.FontStyle = "bold"
Else
Target.Font.ColorIndex = 1: Target.Font.FontStyle = "normal"
End If

Set ComplementRange =
ComplementaryRange(Range("C5:H33"),Range("F5:F33") )

With ComplementRange.Font
.ColorIndex = 1
.FontStyle = "normal"
End With

End If
End Sub

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only, please.

"derek" skrev i en meddelelse
...
Excel 2000
With a previous question and your help I'm up and running
with the code below. But I need a last bit of help please
to find away round a glitch..
Users enter data into the sheet within a range of C5:H33.
For ease they sometime copy and paste part of a row into
the next row but sometime paste it into the wrong cell.
The result is the red/bold format if present in the F5:F33
(from the code below) is transfered to another cell. How
can I ensure that all other cells other than the F5:F33
range remains black and normal when data is enterd either
directly or copied from a cell with red/bold format?

Private Sub workbook_sheetchange(ByVal sh As Object, ByVal
Target As Range)
On Error Resume Next

If Not Intersect(Target, Range("f5:f33")) Is Nothing Then
If Target.Value = 0.5 Then
Target.Font.ColorIndex = 3: Target.Font.FontStyle = "bold"
Else
Target.Font.ColorIndex = 1: Target.Font.FontStyle
= "normal"
End If
End If
End Sub

TIA Derek..



  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 111
Default Help with If Not Intersect

Sorry about the word-wrapping. Here's one
without:

Function ComplementaryRange(SuperRange As Range, _
SubRange As Range) As Range
'Leo Heuser, 11 July 2003
Dim ComplementRange As Range
Dim Counter As Long
Dim PartRange(1 To 4) As Range

On Error Resume Next

Set SubRange = Intersect(SuperRange, SubRange)
With SuperRange
Set PartRange(1) = Cells(.Row, .Column). _
Resize(SubRange.Row - .Row, .Columns.Count)

Set PartRange(2) = Cells(SubRange.Row, .Column). _
Resize(SubRange.Rows.Count, SubRange.Column - .Column)

Set PartRange(3) = Cells(SubRange.Row, SubRange.Column + _
SubRange.Columns.Count). _
Resize(SubRange.Rows.Count, .Columns.Count - _
(SubRange.Column - .Column + SubRange.Columns.Count))

Set PartRange(4) = Cells(SubRange.Row + _
SubRange.Rows.Count, .Column). _
Resize(.Rows.Count - (SubRange.Row - .Row + _
SubRange.Rows.Count), .Columns.Count)
End With

For Counter = 1 To 4
If Not PartRange(Counter) Is Nothing Then
If ComplementRange Is Nothing Then
Set ComplementRange = PartRange(Counter)
Else
Set ComplementRange = Union(ComplementRange, _
PartRange(Counter))
End If
End If
Next Counter

Set ComplementaryRange = ComplementRange
End Function


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
Intersect Line GoodTrouble Charts and Charting in Excel 4 January 29th 08 06:08 PM
Complex Union Intersect [email protected] Excel Worksheet Functions 3 May 23rd 07 12:23 PM
how to find an intersect of two columns sarora New Users to Excel 3 May 31st 06 03:32 AM
Intersect operations heyes Excel Worksheet Functions 4 February 14th 06 06:13 PM
How do I find where a column value and row value intersect? Amy Excel Worksheet Functions 2 January 13th 05 12:19 AM


All times are GMT +1. The time now is 12:43 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"