highlighting cells with formulas
 

I would like to format rows so that when I hightlight a cell it automatically
changes 1's to 0's such as the following:
39880 Alsace Ct. 0 0 0 0 0 0
when highlight address 39880, it would turn all ones to zeros and once you
un-highlight, it would change them back to 1's. Each zero represents a column.

Any suggestions on how to do this???

Thanks - MIke

highlighting cells with formulas
 

You can achieve this creating a macro. You have to insert some code in the
event Worksheet_Selectionchange

here is the code
Public rngOldTarget As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not (rngOldTarget Is Nothing) Then
rngOldTarget.Offset(0, 1).Resize(, 5).Value = 0
End If

Target.Offset(0, 1).Resize(, 5).Value = 1
Set rngOldTarget = Target
End Sub

the number 5 in the resize method is the number of columns you would like to
switch from 0 to 1.

If you want to apply this only to certain range, for example only if the
cell selected is within the range A5:A10 add the following to the code :

If Intersect(Range("A5:A10"), Target) Is Nothing Then
Exit Sub
End If

Gustavo.


"Michael Angelo" wrote:

I would like to format rows so that when I hightlight a cell it automatically
changes 1's to 0's such as the following:
39880 Alsace Ct. 0 0 0 0 0 0
when highlight address 39880, it would turn all ones to zeros and once you
un-highlight, it would change them back to 1's. Each zero represents a column.

Any suggestions on how to do this???

Thanks - MIke

highlighting cells with formulas
 

Gustavo,
Thanks for the input but I'm still lost ( novice here ). Here is what
I'm trying to accomplish. Cell "A" = Address, B,C,D,E,F,G,H all represent
days of the week and all equal 1 for the most part. When "A" goes on vacation
or stops, I would like to highlight that cell in RED at which point it would
convert cells B-H to a 0 and upon return I remove the red from cell "A" and
the numbers covert back to 1's. Does this make any sense at all. Creating a
macro would mean very detailed instructions on how to.

"Gustavo Marinoni" wrote:

You can achieve this creating a macro. You have to insert some code in the
event Worksheet_Selectionchange

here is the code
Public rngOldTarget As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not (rngOldTarget Is Nothing) Then
rngOldTarget.Offset(0, 1).Resize(, 5).Value = 0
End If

Target.Offset(0, 1).Resize(, 5).Value = 1
Set rngOldTarget = Target
End Sub

the number 5 in the resize method is the number of columns you would like to
switch from 0 to 1.

If you want to apply this only to certain range, for example only if the
cell selected is within the range A5:A10 add the following to the code :

If Intersect(Range("A5:A10"), Target) Is Nothing Then
Exit Sub
End If

Gustavo.


"Michael Angelo" wrote:

I would like to format rows so that when I hightlight a cell it automatically
changes 1's to 0's such as the following:
39880 Alsace Ct. 0 0 0 0 0 0
when highlight address 39880, it would turn all ones to zeros and once you
un-highlight, it would change them back to 1's. Each zero represents a column.

Any suggestions on how to do this???

Thanks - MIke

highlighting cells with formulas
 

You could probably be better off using CONTIDITONAL FORMATTING.
formatconditional formatting

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Michael Angelo" wrote in message
...
Gustavo,
Thanks for the input but I'm still lost ( novice here ). Here is what
I'm trying to accomplish. Cell "A" = Address, B,C,D,E,F,G,H all represent
days of the week and all equal 1 for the most part. When "A" goes on
vacation
or stops, I would like to highlight that cell in RED at which point it
would
convert cells B-H to a 0 and upon return I remove the red from cell "A"
and
the numbers covert back to 1's. Does this make any sense at all. Creating
a
macro would mean very detailed instructions on how to.

"Gustavo Marinoni" wrote:

You can achieve this creating a macro. You have to insert some code in
the
event Worksheet_Selectionchange

here is the code
Public rngOldTarget As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not (rngOldTarget Is Nothing) Then
rngOldTarget.Offset(0, 1).Resize(, 5).Value = 0
End If

Target.Offset(0, 1).Resize(, 5).Value = 1
Set rngOldTarget = Target
End Sub

the number 5 in the resize method is the number of columns you would like
to
switch from 0 to 1.

If you want to apply this only to certain range, for example only if the
cell selected is within the range A5:A10 add the following to the code :

If Intersect(Range("A5:A10"), Target) Is Nothing Then
Exit Sub
End If

Gustavo.


"Michael Angelo" wrote:

I would like to format rows so that when I hightlight a cell it
automatically
changes 1's to 0's such as the following:
39880 Alsace Ct. 0 0 0 0 0 0
when highlight address 39880, it would turn all ones to zeros and once
you
un-highlight, it would change them back to 1's. Each zero represents a
column.

Any suggestions on how to do this???

Thanks - MIke