conditional formatting
 

I am creating a simple check sheet which shows what task items have been
checked. The boxes have been formatted to turn a color when a value greater
than zero has been entered. My question - Is there a way to change the color
by just clicking within the cell with your mouse or is it maditory to input a
value in the cell?

conditional formatting
 

You can try this event macro. Assume the range of interest is A1:A10.

Delete any conditional formatting you already have applied to this range.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
If Target.Interior.ColorIndex = 36 Then 'light yellow
Target.Interior.ColorIndex = 0 'white (default)
Else
Target.Interior.ColorIndex = 36
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub

When you select any cell within the defined range it will fill with a light
yellow color. If you select that cell again it will remove the yellow fill
and return back to white (the default).

To install this macro:

Select the sheet where you want this to happen
Select the sheet tab and select View code
Paste the code into the window that opens
ALT Q to return to Excel


--
Biff
Microsoft Excel MVP


"KC" wrote in message
...
I am creating a simple check sheet which shows what task items have been
checked. The boxes have been formatted to turn a color when a value
greater
than zero has been entered. My question - Is there a way to change the
color
by just clicking within the cell with your mouse or is it maditory to
input a
value in the cell?

conditional formatting
 

Thanks for your quick reply T. Valko. Macro works great but is it possible
for the same macro to add a value of X while changing the color for that
cell?

"T. Valko" wrote:

You can try this event macro. Assume the range of interest is A1:A10.

Delete any conditional formatting you already have applied to this range.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
If Target.Interior.ColorIndex = 36 Then 'light yellow
Target.Interior.ColorIndex = 0 'white (default)
Else
Target.Interior.ColorIndex = 36
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub

When you select any cell within the defined range it will fill with a light
yellow color. If you select that cell again it will remove the yellow fill
and return back to white (the default).

To install this macro:

Select the sheet where you want this to happen
Select the sheet tab and select View code
Paste the code into the window that opens
ALT Q to return to Excel


--
Biff
Microsoft Excel MVP


"KC" wrote in message
...
I am creating a simple check sheet which shows what task items have been
checked. The boxes have been formatted to turn a color when a value
greater
than zero has been entered. My question - Is there a way to change the
color
by just clicking within the cell with your mouse or is it maditory to
input a
value in the cell?

conditional formatting
 

Define: add a value of X


--
Biff
Microsoft Excel MVP


"KC" wrote in message
...
Thanks for your quick reply T. Valko. Macro works great but is it
possible
for the same macro to add a value of X while changing the color for that
cell?

"T. Valko" wrote:

You can try this event macro. Assume the range of interest is A1:A10.

Delete any conditional formatting you already have applied to this range.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
If Target.Interior.ColorIndex = 36 Then 'light yellow
Target.Interior.ColorIndex = 0 'white (default)
Else
Target.Interior.ColorIndex = 36
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub

When you select any cell within the defined range it will fill with a
light
yellow color. If you select that cell again it will remove the yellow
fill
and return back to white (the default).

To install this macro:

Select the sheet where you want this to happen
Select the sheet tab and select View code
Paste the code into the window that opens
ALT Q to return to Excel


--
Biff
Microsoft Excel MVP


"KC" wrote in message
...
I am creating a simple check sheet which shows what task items have been
checked. The boxes have been formatted to turn a color when a value
greater
than zero has been entered. My question - Is there a way to change the
color
by just clicking within the cell with your mouse or is it maditory to
input a
value in the cell?

conditional formatting
 

Something as simple as the letter X, I'm trying to calculate percentages.

"T. Valko" wrote:

Define: add a value of X


--
Biff
Microsoft Excel MVP


"KC" wrote in message
...
Thanks for your quick reply T. Valko. Macro works great but is it
possible
for the same macro to add a value of X while changing the color for that
cell?

"T. Valko" wrote:

You can try this event macro. Assume the range of interest is A1:A10.

Delete any conditional formatting you already have applied to this range.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
If Target.Interior.ColorIndex = 36 Then 'light yellow
Target.Interior.ColorIndex = 0 'white (default)
Else
Target.Interior.ColorIndex = 36
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub

When you select any cell within the defined range it will fill with a
light
yellow color. If you select that cell again it will remove the yellow
fill
and return back to white (the default).

To install this macro:

Select the sheet where you want this to happen
Select the sheet tab and select View code
Paste the code into the window that opens
ALT Q to return to Excel


--
Biff
Microsoft Excel MVP


"KC" wrote in message
...
I am creating a simple check sheet which shows what task items have been
checked. The boxes have been formatted to turn a color when a value
greater
than zero has been entered. My question - Is there a way to change the
color
by just clicking within the cell with your mouse or is it maditory to
input a
value in the cell?

conditional formatting
 

Define: add a value of X

Do you mean to literally enter the character "X" ?

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Define: add a value of X


--
Biff
Microsoft Excel MVP


"KC" wrote in message
...
Thanks for your quick reply T. Valko. Macro works great but is it
possible
for the same macro to add a value of X while changing the color for that
cell?

"T. Valko" wrote:

You can try this event macro. Assume the range of interest is A1:A10.

Delete any conditional formatting you already have applied to this
range.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
If Target.Interior.ColorIndex = 36 Then 'light yellow
Target.Interior.ColorIndex = 0 'white (default)
Else
Target.Interior.ColorIndex = 36
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub

When you select any cell within the defined range it will fill with a
light
yellow color. If you select that cell again it will remove the yellow
fill
and return back to white (the default).

To install this macro:

Select the sheet where you want this to happen
Select the sheet tab and select View code
Paste the code into the window that opens
ALT Q to return to Excel


--
Biff
Microsoft Excel MVP


"KC" wrote in message
...
I am creating a simple check sheet which shows what task items have
been
checked. The boxes have been formatted to turn a color when a value
greater
than zero has been entered. My question - Is there a way to change the
color
by just clicking within the cell with your mouse or is it maditory to
input a
value in the cell?

conditional formatting
 

Yes... that will work.

"T. Valko" wrote:

Define: add a value of X

Do you mean to literally enter the character "X" ?

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Define: add a value of X


--
Biff
Microsoft Excel MVP


"KC" wrote in message
...
Thanks for your quick reply T. Valko. Macro works great but is it
possible
for the same macro to add a value of X while changing the color for that
cell?

"T. Valko" wrote:

You can try this event macro. Assume the range of interest is A1:A10.

Delete any conditional formatting you already have applied to this
range.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
If Target.Interior.ColorIndex = 36 Then 'light yellow
Target.Interior.ColorIndex = 0 'white (default)
Else
Target.Interior.ColorIndex = 36
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub

When you select any cell within the defined range it will fill with a
light
yellow color. If you select that cell again it will remove the yellow
fill
and return back to white (the default).

To install this macro:

Select the sheet where you want this to happen
Select the sheet tab and select View code
Paste the code into the window that opens
ALT Q to return to Excel


--
Biff
Microsoft Excel MVP


"KC" wrote in message
...
I am creating a simple check sheet which shows what task items have
been
checked. The boxes have been formatted to turn a color when a value
greater
than zero has been entered. My question - Is there a way to change the
color
by just clicking within the cell with your mouse or is it maditory to
input a
value in the cell?

conditional formatting
 

Ok, this will place a "X" in the cell and color the cell light yellow when
selected.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
If Target.Interior.ColorIndex = 36 Then '36 = light yellow
Target.Clear
Else
Target.Interior.ColorIndex = 36
Target.Value = "X"
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub

--
Biff
Microsoft Excel MVP


"KC" wrote in message
...
Yes... that will work.

"T. Valko" wrote:

Define: add a value of X

Do you mean to literally enter the character "X" ?

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Define: add a value of X


--
Biff
Microsoft Excel MVP


"KC" wrote in message
...
Thanks for your quick reply T. Valko. Macro works great but is it
possible
for the same macro to add a value of X while changing the color for
that
cell?

"T. Valko" wrote:

You can try this event macro. Assume the range of interest is A1:A10.

Delete any conditional formatting you already have applied to this
range.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
If Target.Interior.ColorIndex = 36 Then 'light yellow
Target.Interior.ColorIndex = 0 'white (default)
Else
Target.Interior.ColorIndex = 36
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub

When you select any cell within the defined range it will fill with a
light
yellow color. If you select that cell again it will remove the yellow
fill
and return back to white (the default).

To install this macro:

Select the sheet where you want this to happen
Select the sheet tab and select View code
Paste the code into the window that opens
ALT Q to return to Excel


--
Biff
Microsoft Excel MVP


"KC" wrote in message
...
I am creating a simple check sheet which shows what task items have
been
checked. The boxes have been formatted to turn a color when a value
greater
than zero has been entered. My question - Is there a way to change
the
color
by just clicking within the cell with your mouse or is it maditory
to
input a
value in the cell?

conditional formatting
 

Works great and thanks... Sorry it took so long to answer.

"T. Valko" wrote:

Ok, this will place a "X" in the cell and color the cell light yellow when
selected.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
If Target.Interior.ColorIndex = 36 Then '36 = light yellow
Target.Clear
Else
Target.Interior.ColorIndex = 36
Target.Value = "X"
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub

--
Biff
Microsoft Excel MVP


"KC" wrote in message
...
Yes... that will work.

"T. Valko" wrote:

Define: add a value of X

Do you mean to literally enter the character "X" ?

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Define: add a value of X


--
Biff
Microsoft Excel MVP


"KC" wrote in message
...
Thanks for your quick reply T. Valko. Macro works great but is it
possible
for the same macro to add a value of X while changing the color for
that
cell?

"T. Valko" wrote:

You can try this event macro. Assume the range of interest is A1:A10.

Delete any conditional formatting you already have applied to this
range.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
If Target.Interior.ColorIndex = 36 Then 'light yellow
Target.Interior.ColorIndex = 0 'white (default)
Else
Target.Interior.ColorIndex = 36
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub

When you select any cell within the defined range it will fill with a
light
yellow color. If you select that cell again it will remove the yellow
fill
and return back to white (the default).

To install this macro:

Select the sheet where you want this to happen
Select the sheet tab and select View code
Paste the code into the window that opens
ALT Q to return to Excel


--
Biff
Microsoft Excel MVP


"KC" wrote in message
...
I am creating a simple check sheet which shows what task items have
been
checked. The boxes have been formatted to turn a color when a value
greater
than zero has been entered. My question - Is there a way to change
the
color
by just clicking within the cell with your mouse or is it maditory
to
input a
value in the cell?

conditional formatting
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"KC" wrote in message
...
Works great and thanks... Sorry it took so long to answer.

"T. Valko" wrote:

Ok, this will place a "X" in the cell and color the cell light yellow
when
selected.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
If Target.Interior.ColorIndex = 36 Then '36 = light yellow
Target.Clear
Else
Target.Interior.ColorIndex = 36
Target.Value = "X"
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub

--
Biff
Microsoft Excel MVP


"KC" wrote in message
...
Yes... that will work.

"T. Valko" wrote:

Define: add a value of X

Do you mean to literally enter the character "X" ?

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Define: add a value of X


--
Biff
Microsoft Excel MVP


"KC" wrote in message
...
Thanks for your quick reply T. Valko. Macro works great but is it
possible
for the same macro to add a value of X while changing the color for
that
cell?

"T. Valko" wrote:

You can try this event macro. Assume the range of interest is
A1:A10.

Delete any conditional formatting you already have applied to this
range.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
If Target.Interior.ColorIndex = 36 Then 'light yellow
Target.Interior.ColorIndex = 0 'white (default)
Else
Target.Interior.ColorIndex = 36
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub

When you select any cell within the defined range it will fill
with a
light
yellow color. If you select that cell again it will remove the
yellow
fill
and return back to white (the default).

To install this macro:

Select the sheet where you want this to happen
Select the sheet tab and select View code
Paste the code into the window that opens
ALT Q to return to Excel


--
Biff
Microsoft Excel MVP


"KC" wrote in message
...
I am creating a simple check sheet which shows what task items
have
been
checked. The boxes have been formatted to turn a color when a
value
greater
than zero has been entered. My question - Is there a way to
change
the
color
by just clicking within the cell with your mouse or is it
maditory
to
input a
value in the cell?