Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional Formatting | Excel Discussion (Misc queries) | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |