| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
best way to format on cell change
I want to color a row from column G to AB if range("p" & currentrow) value
changes... currentrow being the row of the cell value that was changed... how do select the row of the cell that was changed? Thanks for the help |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
best way to format on cell change
Private Sub Worksheet_Change(ByVal Target As Range)
if Target.Column = 16 then Cells(Target.Row,"G").Resize(1,22).Interior.ColorI ndex = 3 End if End Sub Right click on the sheet tab and select view Code. -- Regards, Tom Ogilvy "John" wrote in message ... I want to color a row from column G to AB if range("p" & currentrow) value changes... currentrow being the row of the cell value that was changed... how do select the row of the cell that was changed? Thanks for the help |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
best way to format on cell change
Tom, thanks for the reply. Nothing changes when my cell value changes (it
changes to true of false)... here is the code... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 16 Then If Target.Value = "true" Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 If Target.Value = "false" Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 0 End If End Sub "Tom Ogilvy" wrote: Private Sub Worksheet_Change(ByVal Target As Range) if Target.Column = 16 then Cells(Target.Row,"G").Resize(1,22).Interior.ColorI ndex = 3 End if End Sub Right click on the sheet tab and select view Code. -- Regards, Tom Ogilvy "John" wrote in message ... I want to color a row from column G to AB if range("p" & currentrow) value changes... currentrow being the row of the cell value that was changed... how do select the row of the cell that was changed? Thanks for the help |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
best way to format on cell change
I interpreted your requirement for someone manually making an entry in
column P. If that isn't the requirement, try this revision: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Value = True Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 If Target.Value = False Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = xlNone End If End Sub -- Regards, Tom Ogilvy "John" wrote in message ... Tom, thanks for the reply. Nothing changes when my cell value changes (it changes to true of false)... here is the code... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 16 Then If Target.Value = "true" Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 If Target.Value = "false" Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 0 End If End Sub "Tom Ogilvy" wrote: Private Sub Worksheet_Change(ByVal Target As Range) if Target.Column = 16 then Cells(Target.Row,"G").Resize(1,22).Interior.ColorI ndex = 3 End if End Sub Right click on the sheet tab and select view Code. -- Regards, Tom Ogilvy "John" wrote in message ... I want to color a row from column G to AB if range("p" & currentrow) value changes... currentrow being the row of the cell value that was changed... how do select the row of the cell that was changed? Thanks for the help |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
best way to format on cell change
Tom I appreciate the help, that works. However, my true and false are linked
to checkboxes (if checked cell equals true, if not cell equals false)... if I check or uncheck my check boxes, which changes column P values, it does not do the formating. Is this because of the checkboxes? Thanks again... "Tom Ogilvy" wrote: I interpreted your requirement for someone manually making an entry in column P. If that isn't the requirement, try this revision: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Value = True Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 If Target.Value = False Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = xlNone End If End Sub -- Regards, Tom Ogilvy "John" wrote in message ... Tom, thanks for the reply. Nothing changes when my cell value changes (it changes to true of false)... here is the code... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 16 Then If Target.Value = "true" Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 If Target.Value = "false" Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 0 End If End Sub "Tom Ogilvy" wrote: Private Sub Worksheet_Change(ByVal Target As Range) if Target.Column = 16 then Cells(Target.Row,"G").Resize(1,22).Interior.ColorI ndex = 3 End if End Sub Right click on the sheet tab and select view Code. -- Regards, Tom Ogilvy "John" wrote in message ... I want to color a row from column G to AB if range("p" & currentrow) value changes... currentrow being the row of the cell value that was changed... how do select the row of the cell that was changed? Thanks for the help |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
best way to format on cell change
Are the checkboxes from the control toolbox toolbar or from the forms
toolbar? Are all the checkboxes located in the same column and positioned over the row you want to format? If the checkboxes are from the control toolbox toolbar, are you familiar with class modules? In any event, are there formulas that refer to the cells in column P that would cause a recalculate if their value changed? -- Regards, Tom Ogilvy "John" wrote in message ... Tom I appreciate the help, that works. However, my true and false are linked to checkboxes (if checked cell equals true, if not cell equals false)... if I check or uncheck my check boxes, which changes column P values, it does not do the formating. Is this because of the checkboxes? Thanks again... "Tom Ogilvy" wrote: I interpreted your requirement for someone manually making an entry in column P. If that isn't the requirement, try this revision: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Value = True Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 If Target.Value = False Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = xlNone End If End Sub -- Regards, Tom Ogilvy "John" wrote in message ... Tom, thanks for the reply. Nothing changes when my cell value changes (it changes to true of false)... here is the code... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 16 Then If Target.Value = "true" Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 If Target.Value = "false" Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 0 End If End Sub "Tom Ogilvy" wrote: Private Sub Worksheet_Change(ByVal Target As Range) if Target.Column = 16 then Cells(Target.Row,"G").Resize(1,22).Interior.ColorI ndex = 3 End if End Sub Right click on the sheet tab and select view Code. -- Regards, Tom Ogilvy "John" wrote in message ... I want to color a row from column G to AB if range("p" & currentrow) value changes... currentrow being the row of the cell value that was changed... how do select the row of the cell that was changed? Thanks for the help |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
best way to format on cell change
They are from the control toolbox, they are all positioned down the same
column and positioned over the row I want to format.... are class modules regular VBA modules? there are formulas that change in column U that calculate off of these checkboxes... "Tom Ogilvy" wrote: Are the checkboxes from the control toolbox toolbar or from the forms toolbar? Are all the checkboxes located in the same column and positioned over the row you want to format? If the checkboxes are from the control toolbox toolbar, are you familiar with class modules? In any event, are there formulas that refer to the cells in column P that would cause a recalculate if their value changed? -- Regards, Tom Ogilvy "John" wrote in message ... Tom I appreciate the help, that works. However, my true and false are linked to checkboxes (if checked cell equals true, if not cell equals false)... if I check or uncheck my check boxes, which changes column P values, it does not do the formating. Is this because of the checkboxes? Thanks again... "Tom Ogilvy" wrote: I interpreted your requirement for someone manually making an entry in column P. If that isn't the requirement, try this revision: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Value = True Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 If Target.Value = False Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = xlNone End If End Sub -- Regards, Tom Ogilvy "John" wrote in message ... Tom, thanks for the reply. Nothing changes when my cell value changes (it changes to true of false)... here is the code... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 16 Then If Target.Value = "true" Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 If Target.Value = "false" Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 0 End If End Sub "Tom Ogilvy" wrote: Private Sub Worksheet_Change(ByVal Target As Range) if Target.Column = 16 then Cells(Target.Row,"G").Resize(1,22).Interior.ColorI ndex = 3 End if End Sub Right click on the sheet tab and select view Code. -- Regards, Tom Ogilvy "John" wrote in message ... I want to color a row from column G to AB if range("p" & currentrow) value changes... currentrow being the row of the cell value that was changed... how do select the row of the cell that was changed? Thanks for the help |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
best way to format on cell change
As long a calculation is set to automatic, the easiest would just be to
process every row Private Sub Worksheets_Calculate() Dim Target as Range for each Target in Range("P2:P50") If Target.Value = True Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 End If If Target.Value = False Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = xlNone End If Next End Sub If that is too slow then post back. Change P2:P50 to refer to the cells you want to manage. -- Regards, Tom Ogilvy "John" wrote in message ... They are from the control toolbox, they are all positioned down the same column and positioned over the row I want to format.... are class modules regular VBA modules? there are formulas that change in column U that calculate off of these checkboxes... "Tom Ogilvy" wrote: Are the checkboxes from the control toolbox toolbar or from the forms toolbar? Are all the checkboxes located in the same column and positioned over the row you want to format? If the checkboxes are from the control toolbox toolbar, are you familiar with class modules? In any event, are there formulas that refer to the cells in column P that would cause a recalculate if their value changed? -- Regards, Tom Ogilvy "John" wrote in message ... Tom I appreciate the help, that works. However, my true and false are linked to checkboxes (if checked cell equals true, if not cell equals false)... if I check or uncheck my check boxes, which changes column P values, it does not do the formating. Is this because of the checkboxes? Thanks again... "Tom Ogilvy" wrote: I interpreted your requirement for someone manually making an entry in column P. If that isn't the requirement, try this revision: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Value = True Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 If Target.Value = False Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = xlNone End If End Sub -- Regards, Tom Ogilvy "John" wrote in message ... Tom, thanks for the reply. Nothing changes when my cell value changes (it changes to true of false)... here is the code... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 16 Then If Target.Value = "true" Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 If Target.Value = "false" Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 0 End If End Sub "Tom Ogilvy" wrote: Private Sub Worksheet_Change(ByVal Target As Range) if Target.Column = 16 then Cells(Target.Row,"G").Resize(1,22).Interior.ColorI ndex = 3 End if End Sub Right click on the sheet tab and select view Code. -- Regards, Tom Ogilvy "John" wrote in message ... I want to color a row from column G to AB if range("p" & currentrow) value changes... currentrow being the row of the cell value that was changed... how do select the row of the cell that was changed? Thanks for the help |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
best way to format on cell change
Your code doesn't change anything... either do these two variations... which
attempt to get it to format on a change in column U which calculates off of the checkbox... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 20 Then If Target.Value 0.0001 Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 End If If Target.Value = 0 Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = xlNone End If End If End Sub OR Private Sub Worksheets_Calculate() Dim Target As Range For Each Target In Range("u3:u28") If Target.Value 0.0001 Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 End If If Target.Value = 0 Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = xlNone End If Next End Sub "Tom Ogilvy" wrote: As long a calculation is set to automatic, the easiest would just be to process every row Private Sub Worksheets_Calculate() Dim Target as Range for each Target in Range("P2:P50") If Target.Value = True Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 End If If Target.Value = False Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = xlNone End If Next End Sub If that is too slow then post back. Change P2:P50 to refer to the cells you want to manage. -- Regards, Tom Ogilvy "John" wrote in message ... They are from the control toolbox, they are all positioned down the same column and positioned over the row I want to format.... are class modules regular VBA modules? there are formulas that change in column U that calculate off of these checkboxes... "Tom Ogilvy" wrote: Are the checkboxes from the control toolbox toolbar or from the forms toolbar? Are all the checkboxes located in the same column and positioned over the row you want to format? If the checkboxes are from the control toolbox toolbar, are you familiar with class modules? In any event, are there formulas that refer to the cells in column P that would cause a recalculate if their value changed? -- Regards, Tom Ogilvy "John" wrote in message ... Tom I appreciate the help, that works. However, my true and false are linked to checkboxes (if checked cell equals true, if not cell equals false)... if I check or uncheck my check boxes, which changes column P values, it does not do the formating. Is this because of the checkboxes? Thanks again... "Tom Ogilvy" wrote: I interpreted your requirement for someone manually making an entry in column P. If that isn't the requirement, try this revision: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Value = True Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 If Target.Value = False Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = xlNone End If End Sub -- Regards, Tom Ogilvy "John" wrote in message ... Tom, thanks for the reply. Nothing changes when my cell value changes (it changes to true of false)... here is the code... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 16 Then If Target.Value = "true" Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 If Target.Value = "false" Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 0 End If End Sub "Tom Ogilvy" wrote: Private Sub Worksheet_Change(ByVal Target As Range) if Target.Column = 16 then Cells(Target.Row,"G").Resize(1,22).Interior.ColorI ndex = 3 End if End Sub Right click on the sheet tab and select view Code. -- Regards, Tom Ogilvy "John" wrote in message ... I want to color a row from column G to AB if range("p" & currentrow) value changes... currentrow being the row of the cell value that was changed... how do select the row of the cell that was changed? Thanks for the help |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
best way to format on cell change
This code will not work if just one cell is changed!!!! I am confused....
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "u4" Then If Target.Value 0.00001 Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 Else Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = xlNone End If End If End Sub "Tom Ogilvy" wrote: As long a calculation is set to automatic, the easiest would just be to process every row Private Sub Worksheets_Calculate() Dim Target as Range for each Target in Range("P2:P50") If Target.Value = True Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 End If If Target.Value = False Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = xlNone End If Next End Sub If that is too slow then post back. Change P2:P50 to refer to the cells you want to manage. -- Regards, Tom Ogilvy "John" wrote in message ... They are from the control toolbox, they are all positioned down the same column and positioned over the row I want to format.... are class modules regular VBA modules? there are formulas that change in column U that calculate off of these checkboxes... "Tom Ogilvy" wrote: Are the checkboxes from the control toolbox toolbar or from the forms toolbar? Are all the checkboxes located in the same column and positioned over the row you want to format? If the checkboxes are from the control toolbox toolbar, are you familiar with class modules? In any event, are there formulas that refer to the cells in column P that would cause a recalculate if their value changed? -- Regards, Tom Ogilvy "John" wrote in message ... Tom I appreciate the help, that works. However, my true and false are linked to checkboxes (if checked cell equals true, if not cell equals false)... if I check or uncheck my check boxes, which changes column P values, it does not do the formating. Is this because of the checkboxes? Thanks again... "Tom Ogilvy" wrote: I interpreted your requirement for someone manually making an entry in column P. If that isn't the requirement, try this revision: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Value = True Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 If Target.Value = False Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = xlNone End If End Sub -- Regards, Tom Ogilvy "John" wrote in message ... Tom, thanks for the reply. Nothing changes when my cell value changes (it changes to true of false)... here is the code... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 16 Then If Target.Value = "true" Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 If Target.Value = "false" Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 0 End If End Sub "Tom Ogilvy" wrote: Private Sub Worksheet_Change(ByVal Target As Range) if Target.Column = 16 then Cells(Target.Row,"G").Resize(1,22).Interior.ColorI ndex = 3 End if End Sub Right click on the sheet tab and select view Code. -- Regards, Tom Ogilvy "John" wrote in message ... I want to color a row from column G to AB if range("p" & currentrow) value changes... currentrow being the row of the cell value that was changed... how do select the row of the cell that was changed? Thanks for the help |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
best way to format on cell change
My typo Calculates should be Calculate
Private Sub Worksheet_Calculate() MsgBox "In Calculate" Dim Target As Range For Each Target In Range("u3:u28") If Target.Value 0.0001 Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 End If If Target.Value = 0 Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = xlNone End If Next End Sub worked fine for me. -- Regards, Tom Ogilvy "John" wrote in message ... This code will not work if just one cell is changed!!!! I am confused.... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "u4" Then If Target.Value 0.00001 Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 Else Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = xlNone End If End If End Sub "Tom Ogilvy" wrote: As long a calculation is set to automatic, the easiest would just be to process every row Private Sub Worksheets_Calculate() Dim Target as Range for each Target in Range("P2:P50") If Target.Value = True Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 End If If Target.Value = False Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = xlNone End If Next End Sub If that is too slow then post back. Change P2:P50 to refer to the cells you want to manage. -- Regards, Tom Ogilvy "John" wrote in message ... They are from the control toolbox, they are all positioned down the same column and positioned over the row I want to format.... are class modules regular VBA modules? there are formulas that change in column U that calculate off of these checkboxes... "Tom Ogilvy" wrote: Are the checkboxes from the control toolbox toolbar or from the forms toolbar? Are all the checkboxes located in the same column and positioned over the row you want to format? If the checkboxes are from the control toolbox toolbar, are you familiar with class modules? In any event, are there formulas that refer to the cells in column P that would cause a recalculate if their value changed? -- Regards, Tom Ogilvy "John" wrote in message ... Tom I appreciate the help, that works. However, my true and false are linked to checkboxes (if checked cell equals true, if not cell equals false)... if I check or uncheck my check boxes, which changes column P values, it does not do the formating. Is this because of the checkboxes? Thanks again... "Tom Ogilvy" wrote: I interpreted your requirement for someone manually making an entry in column P. If that isn't the requirement, try this revision: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Value = True Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 If Target.Value = False Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = xlNone End If End Sub -- Regards, Tom Ogilvy "John" wrote in message ... Tom, thanks for the reply. Nothing changes when my cell value changes (it changes to true of false)... here is the code... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 16 Then If Target.Value = "true" Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 If Target.Value = "false" Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 0 End If End Sub "Tom Ogilvy" wrote: Private Sub Worksheet_Change(ByVal Target As Range) if Target.Column = 16 then Cells(Target.Row,"G").Resize(1,22).Interior.ColorI ndex = 3 End if End Sub Right click on the sheet tab and select view Code. -- Regards, Tom Ogilvy "John" wrote in message ... I want to color a row from column G to AB if range("p" & currentrow) value changes... currentrow being the row of the cell value that was changed... how do select the row of the cell that was changed? Thanks for the help |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
best way to format on cell change
That colors rows 3 through 28 for me... not selecting the ones that meet my
criteria "Tom Ogilvy" wrote: My typo Calculates should be Calculate Private Sub Worksheet_Calculate() MsgBox "In Calculate" Dim Target As Range For Each Target In Range("u3:u28") If Target.Value 0.0001 Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 End If If Target.Value = 0 Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = xlNone End If Next End Sub worked fine for me. -- Regards, Tom Ogilvy "John" wrote in message ... This code will not work if just one cell is changed!!!! I am confused.... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "u4" Then If Target.Value 0.00001 Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 Else Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = xlNone End If End If End Sub "Tom Ogilvy" wrote: As long a calculation is set to automatic, the easiest would just be to process every row Private Sub Worksheets_Calculate() Dim Target as Range for each Target in Range("P2:P50") If Target.Value = True Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 End If If Target.Value = False Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = xlNone End If Next End Sub If that is too slow then post back. Change P2:P50 to refer to the cells you want to manage. -- Regards, Tom Ogilvy "John" wrote in message ... They are from the control toolbox, they are all positioned down the same column and positioned over the row I want to format.... are class modules regular VBA modules? there are formulas that change in column U that calculate off of these checkboxes... "Tom Ogilvy" wrote: Are the checkboxes from the control toolbox toolbar or from the forms toolbar? Are all the checkboxes located in the same column and positioned over the row you want to format? If the checkboxes are from the control toolbox toolbar, are you familiar with class modules? In any event, are there formulas that refer to the cells in column P that would cause a recalculate if their value changed? -- Regards, Tom Ogilvy "John" wrote in message ... Tom I appreciate the help, that works. However, my true and false are linked to checkboxes (if checked cell equals true, if not cell equals false)... if I check or uncheck my check boxes, which changes column P values, it does not do the formating. Is this because of the checkboxes? Thanks again... "Tom Ogilvy" wrote: I interpreted your requirement for someone manually making an entry in column P. If that isn't the requirement, try this revision: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Value = True Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 If Target.Value = False Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = xlNone End If End Sub -- Regards, Tom Ogilvy "John" wrote in message ... Tom, thanks for the reply. Nothing changes when my cell value changes (it changes to true of false)... here is the code... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 16 Then If Target.Value = "true" Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 If Target.Value = "false" Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 0 End If End Sub "Tom Ogilvy" wrote: Private Sub Worksheet_Change(ByVal Target As Range) if Target.Column = 16 then Cells(Target.Row,"G").Resize(1,22).Interior.ColorI ndex = 3 End if End Sub Right click on the sheet tab and select view Code. -- Regards, Tom Ogilvy "John" wrote in message ... I want to color a row from column G to AB if range("p" & currentrow) value changes... currentrow being the row of the cell value that was changed... how do select the row of the cell that was changed? Thanks for the help |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
best way to format on cell change
You wrote the criteria???
-- Regards, Tom Ogilvy "John" wrote in message ... That colors rows 3 through 28 for me... not selecting the ones that meet my criteria "Tom Ogilvy" wrote: My typo Calculates should be Calculate Private Sub Worksheet_Calculate() MsgBox "In Calculate" Dim Target As Range For Each Target In Range("u3:u28") If Target.Value 0.0001 Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 End If If Target.Value = 0 Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = xlNone End If Next End Sub worked fine for me. -- Regards, Tom Ogilvy "John" wrote in message ... This code will not work if just one cell is changed!!!! I am confused.... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "u4" Then If Target.Value 0.00001 Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 Else Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = xlNone End If End If End Sub "Tom Ogilvy" wrote: As long a calculation is set to automatic, the easiest would just be to process every row Private Sub Worksheets_Calculate() Dim Target as Range for each Target in Range("P2:P50") If Target.Value = True Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 End If If Target.Value = False Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = xlNone End If Next End Sub If that is too slow then post back. Change P2:P50 to refer to the cells you want to manage. -- Regards, Tom Ogilvy "John" wrote in message ... They are from the control toolbox, they are all positioned down the same column and positioned over the row I want to format.... are class modules regular VBA modules? there are formulas that change in column U that calculate off of these checkboxes... "Tom Ogilvy" wrote: Are the checkboxes from the control toolbox toolbar or from the forms toolbar? Are all the checkboxes located in the same column and positioned over the row you want to format? If the checkboxes are from the control toolbox toolbar, are you familiar with class modules? In any event, are there formulas that refer to the cells in column P that would cause a recalculate if their value changed? -- Regards, Tom Ogilvy "John" wrote in message ... Tom I appreciate the help, that works. However, my true and false are linked to checkboxes (if checked cell equals true, if not cell equals false)... if I check or uncheck my check boxes, which changes column P values, it does not do the formating. Is this because of the checkboxes? Thanks again... "Tom Ogilvy" wrote: I interpreted your requirement for someone manually making an entry in column P. If that isn't the requirement, try this revision: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Value = True Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 If Target.Value = False Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = xlNone End If End Sub -- Regards, Tom Ogilvy "John" wrote in message ... Tom, thanks for the reply. Nothing changes when my cell value changes (it changes to true of false)... here is the code... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 16 Then If Target.Value = "true" Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 15 If Target.Value = "false" Then Cells(Target.Row, "G").Resize(1, 22).Interior.ColorIndex = 0 End If End Sub "Tom Ogilvy" wrote: Private Sub Worksheet_Change(ByVal Target As Range) if Target.Column = 16 then Cells(Target.Row,"G").Resize(1,22).Interior.ColorI ndex = 3 End if End Sub Right click on the sheet tab and select view Code. -- Regards, Tom Ogilvy "John" wrote in message ... I want to color a row from column G to AB if range("p" & currentrow) value changes... currentrow being the row of the cell value that was changed... how do select the row of the cell that was changed? Thanks for the help |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lock Cell Format - Allow copy and paste of data without format change | Excel Worksheet Functions | |||
| Changing the cell format doesn't change existing cell content | Excel Discussion (Misc queries) | |||
| Change Currency Format of Cell based on another Cell | Excel Worksheet Functions | |||
| Can cell format come from and change with reference cell format | Excel Discussion (Misc queries) | |||
| find word in a cell then change cell format | Excel Programming | |||
Linear Mode
