|
need help with a bit of proggraming
Hi all. I need help with some programing. I hope i explain this
right. Also if whom ever helps me please use remarks so I can learn what you did. ok here goes: i let me set this up. these are the name of some of the collums: a b d e f g-s row | app id | name | agent | lease approval | com Paid | ....... | --------------------------------------------------------------------------------- 1 1234 joe john A+, A thru E Yes or No varys or PENDING what i need to do is change the color of the row i have the active cell in. i want that row to change from collum a thru s in the active cell row. it has to have the color change to light green (green for money hehehe) one thing though i really need it to not change any other row but the one with the active cell. so the commpaid can be changed in any row and only that row will be changed. ok here is another tidbit. it also has to check if collum e has letters in it or PENDING if pending then must be error and no change happens. i had an earlier post on this newgroup and got an answer not exact what i wanted so i tried to change it but unsucessful at it. pob my fualt i didnt explain it right so i am trying again. Please use rem statments so i can learn how to change it in case i didnt explain right so i can try and mod it. thanks oh here is the other sub that was writen maybe it will help also Sub ColorCells() Dim cell As Range For Each cell In Selection With Range(cell, cell.Offset(0, 18)).Interior Select Case cell Case "pending" .ColorIndex = 6 Case "yes" .ColorIndex = 2 Case "e" .ColorIndex = 3 Case Is < "pending" .ColorIndex = 50 End Select End With Next cell End Sub i tried to use this by creating a function to call it here is what i tried to do Function colorchangerow(commpaid, leasescore) 'this module will call three different subs to change the color of the rows 'th row will be changed as follow a thru d will be clear 'the pending row will be color 'the paid comision will be green ' commpaid is taken from the e collom and the leasegrade is taken from the f collum commpaid = "No" Application.Volatile If commpaid = "No" And leasescore = "PENDING" Then 'this for when we just input in and are waiting for lease score Call ColorCells ElseIf commpaid = "No" And leasescore < "PENDING" Then 'this for when the lease score is in Call ColorCell1 ElseIf commpaid = "Yes" And leasescore < "PENDING" Then ' this is for color change when commision are paid Call ColorCell2 End If End Function i hope this help sorry if it is not understandable. thanks again |
need help with a bit of proggraming
You can do what you want with conditional formating. Change Value Is to
formula Is. You can have up to 3 differnt colors using 3 different formats for the cell ( Add additional Format). The formula below checks for last row by comparing the number of cells that are not blank with the row number. One you get the formula correct than copy the cell with the formula and use Paste Special - Formats to copy the formula to other cells. =IF(AND(COUNTA($A:$A)=ROW($A4),$E4="PENDING"),TRUE ,FALSE) "exor" wrote: Hi all. I need help with some programing. I hope i explain this right. Also if whom ever helps me please use remarks so I can learn what you did. ok here goes: i let me set this up. these are the name of some of the collums: a b d e f g-s row | app id | name | agent | lease approval | com Paid | ....... | --------------------------------------------------------------------------------- 1 1234 joe john A+, A thru E Yes or No varys or PENDING what i need to do is change the color of the row i have the active cell in. i want that row to change from collum a thru s in the active cell row. it has to have the color change to light green (green for money hehehe) one thing though i really need it to not change any other row but the one with the active cell. so the commpaid can be changed in any row and only that row will be changed. ok here is another tidbit. it also has to check if collum e has letters in it or PENDING if pending then must be error and no change happens. i had an earlier post on this newgroup and got an answer not exact what i wanted so i tried to change it but unsucessful at it. pob my fualt i didnt explain it right so i am trying again. Please use rem statments so i can learn how to change it in case i didnt explain right so i can try and mod it. thanks oh here is the other sub that was writen maybe it will help also Sub ColorCells() Dim cell As Range For Each cell In Selection With Range(cell, cell.Offset(0, 18)).Interior Select Case cell Case "pending" .ColorIndex = 6 Case "yes" .ColorIndex = 2 Case "e" .ColorIndex = 3 Case Is < "pending" .ColorIndex = 50 End Select End With Next cell End Sub i tried to use this by creating a function to call it here is what i tried to do Function colorchangerow(commpaid, leasescore) 'this module will call three different subs to change the color of the rows 'th row will be changed as follow a thru d will be clear 'the pending row will be color 'the paid comision will be green ' commpaid is taken from the e collom and the leasegrade is taken from the f collum commpaid = "No" Application.Volatile If commpaid = "No" And leasescore = "PENDING" Then 'this for when we just input in and are waiting for lease score Call ColorCells ElseIf commpaid = "No" And leasescore < "PENDING" Then 'this for when the lease score is in Call ColorCell1 ElseIf commpaid = "Yes" And leasescore < "PENDING" Then ' this is for color change when commision are paid Call ColorCell2 End If End Function i hope this help sorry if it is not understandable. thanks again |
need help with a bit of proggraming
On Sep 30, 6:51*pm, "Don Guillett" wrote:
Right click sheet tabview codecopy/paste thischange range to suitSAVE workbook. Now, when you change any cell in the range the macro will fire. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("a2:s22")) Is Nothing Then Exit Sub tr = Target.Row If UCase(Target) = "PENDING" Then mi = 0 Else mi = 50 End If Range(Cells(tr, "a"), Cells(tr, "s")).Interior.ColorIndex = mi End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "exor" wrote in message ... Hi all. *I need help with some programing. I hope i explain this right. *Also if whom ever helps me *please use remarks so *I can learn what you did. *ok here goes: i let me set this up. these are the name of some of the collums: * * * * a * * * * * * *b * * * * * d * * * * * * *e f * * * * * * g-s row | app id * | name *| agent | lease approval | com Paid | ........ | ---------------------------------------------------------------------------*------ *1 * * 1234 * * * *joe * * * *john * * *A+, A thru E *Yes or No varys * * * * * * * * * * * * * * * * * * * * * * * * * * * or * * * * * * * * * * * * * * * * * * * * * * * * *PENDING what i need to do is change the color of the row i have the active cell in. i want that *row to change from *collum a thru s in the active cell row. it has to have the color change to light green (green for money hehehe) one thing though i really need it to not change any other row but the one with the active cell. so the commpaid can be changed in any row and only that row will be changed. ok here is another tidbit. *it also has to check if collum e has letters in it or PENDING if pending then must be error and no change happens. i had an earlier post on this newgroup and got an answer not exact what i wanted so i tried to change it but unsucessful at it. pob my fualt i didnt explain it right so i am trying again. Please use rem statments so i can learn how to change it in case i didnt explain right so i can try and mod it. thanks oh here is the other sub that was writen maybe it will help also Sub ColorCells() * *Dim cell As Range * *For Each cell In Selection * * * *With Range(cell, cell.Offset(0, 18)).Interior * * * * * *Select Case cell * * * * * * * *Case "pending" * * * * * * * * * *.ColorIndex = 6 * * * * * * * *Case "yes" * * * * * * * * * *.ColorIndex = 2 * * * * * * * *Case "e" * * * * * * * * * *.ColorIndex = 3 * * * * * * * *Case Is < "pending" * * * * * * * * * *.ColorIndex = 50 * * * * * *End Select * * * *End With * *Next cell End Sub i tried to use this by creating a function to call it here is what i tried to do Function colorchangerow(commpaid, leasescore) 'this module will call three different subs to change the color of the rows 'th row will be changed as follow a thru d will be clear 'the pending row will be color 'the paid comision will be green ' commpaid is taken from the *e collom and the leasegrade is taken from the f collum commpaid = "No" Application.Volatile * *If commpaid = "No" And leasescore = "PENDING" Then 'this for when we just input in and are waiting for lease score * * * *Call ColorCells * *ElseIf commpaid = "No" And leasescore < "PENDING" Then *'this for when the lease score is in * * * *Call ColorCell1 * *ElseIf commpaid = "Yes" And leasescore < "PENDING" Then ' this is for color change when commision are paid * * * *Call ColorCell2 * *End If End Function i hope this help sorry if it is not understandable. thanks again- Hide quoted text - - Show quoted text - ok dont understand how these will work but i will look up in vb book and try to figure the code out. so i can use it. sorry that i dont understand it. it been years since i programed though it would come back like riding a bike. thanks |
need help with a bit of proggraming
|
need help with a bit of proggraming
exor,
You basically want to highlight the row the cell pointer is on from column A thru S, as long as the word "PENDING" is not in column E of the active row. So if the pointer is in row 5 and E5 value is not "PENDING" then the row should be highlighted light green. Here are my questions: When you move to row 6 do you want row 5 to return to normal - meaning it no longer is highlighted while row 6 is highlight if E6 value is not "PENDING"? "exor" wrote: Hi all. I need help with some programing. I hope i explain this right. Also if whom ever helps me please use remarks so I can learn what you did. ok here goes: i let me set this up. these are the name of some of the collums: a b d e f g-s row | app id | name | agent | lease approval | com Paid | ....... | --------------------------------------------------------------------------------- 1 1234 joe john A+, A thru E Yes or No varys or PENDING what i need to do is change the color of the row i have the active cell in. i want that row to change from collum a thru s in the active cell row. it has to have the color change to light green (green for money hehehe) one thing though i really need it to not change any other row but the one with the active cell. so the commpaid can be changed in any row and only that row will be changed. ok here is another tidbit. it also has to check if collum e has letters in it or PENDING if pending then must be error and no change happens. i had an earlier post on this newgroup and got an answer not exact what i wanted so i tried to change it but unsucessful at it. pob my fualt i didnt explain it right so i am trying again. Please use rem statments so i can learn how to change it in case i didnt explain right so i can try and mod it. thanks oh here is the other sub that was writen maybe it will help also Sub ColorCells() Dim cell As Range For Each cell In Selection With Range(cell, cell.Offset(0, 18)).Interior Select Case cell Case "pending" .ColorIndex = 6 Case "yes" .ColorIndex = 2 Case "e" .ColorIndex = 3 Case Is < "pending" .ColorIndex = 50 End Select End With Next cell End Sub i tried to use this by creating a function to call it here is what i tried to do Function colorchangerow(commpaid, leasescore) 'this module will call three different subs to change the color of the rows 'th row will be changed as follow a thru d will be clear 'the pending row will be color 'the paid comision will be green ' commpaid is taken from the e collom and the leasegrade is taken from the f collum commpaid = "No" Application.Volatile If commpaid = "No" And leasescore = "PENDING" Then 'this for when we just input in and are waiting for lease score Call ColorCells ElseIf commpaid = "No" And leasescore < "PENDING" Then 'this for when the lease score is in Call ColorCell1 ElseIf commpaid = "Yes" And leasescore < "PENDING" Then ' this is for color change when commision are paid Call ColorCell2 End If End Function i hope this help sorry if it is not understandable. thanks again |
need help with a bit of proggraming
If what your looking for is highlighting that moves. Copy the below into the
"ThisWorkbook" Object. Save file and open. I did not include the IF THEN to test for "Pending" in column E. Private WithEvents App As Application Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) With Range("A1:S100").Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Ar = ActiveCell.Row With Range(Cells(Ar, "a"), Cells(Ar, "s")).Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent3 .TintAndShade = 0.799981688894314 .PatternTintAndShade = 0 End With End Sub Private Sub Workbook_Open() Set App = Application End Sub "exor" wrote: Hi all. I need help with some programing. I hope i explain this right. Also if whom ever helps me please use remarks so I can learn what you did. ok here goes: i let me set this up. these are the name of some of the collums: a b d e f g-s row | app id | name | agent | lease approval | com Paid | ....... | --------------------------------------------------------------------------------- 1 1234 joe john A+, A thru E Yes or No varys or PENDING what i need to do is change the color of the row i have the active cell in. i want that row to change from collum a thru s in the active cell row. it has to have the color change to light green (green for money hehehe) one thing though i really need it to not change any other row but the one with the active cell. so the commpaid can be changed in any row and only that row will be changed. ok here is another tidbit. it also has to check if collum e has letters in it or PENDING if pending then must be error and no change happens. i had an earlier post on this newgroup and got an answer not exact what i wanted so i tried to change it but unsucessful at it. pob my fualt i didnt explain it right so i am trying again. Please use rem statments so i can learn how to change it in case i didnt explain right so i can try and mod it. thanks oh here is the other sub that was writen maybe it will help also Sub ColorCells() Dim cell As Range For Each cell In Selection With Range(cell, cell.Offset(0, 18)).Interior Select Case cell Case "pending" .ColorIndex = 6 Case "yes" .ColorIndex = 2 Case "e" .ColorIndex = 3 Case Is < "pending" .ColorIndex = 50 End Select End With Next cell End Sub i tried to use this by creating a function to call it here is what i tried to do Function colorchangerow(commpaid, leasescore) 'this module will call three different subs to change the color of the rows 'th row will be changed as follow a thru d will be clear 'the pending row will be color 'the paid comision will be green ' commpaid is taken from the e collom and the leasegrade is taken from the f collum commpaid = "No" Application.Volatile If commpaid = "No" And leasescore = "PENDING" Then 'this for when we just input in and are waiting for lease score Call ColorCells ElseIf commpaid = "No" And leasescore < "PENDING" Then 'this for when the lease score is in Call ColorCell1 ElseIf commpaid = "Yes" And leasescore < "PENDING" Then ' this is for color change when commision are paid Call ColorCell2 End If End Function i hope this help sorry if it is not understandable. thanks again |
need help with a bit of proggraming
Sorry, but the steps to make it work are more detailed.
1. First copy below into the "ThisWorkbook" Object. Private WithEvents App As Application Private Sub Workbook_Open() Set App = Application End Sub 2. Select "App" in the drop down where you see "Workbook". 3. Select "SheetSelectionChange" in the drop down on the right. 4. Then past the below within the Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) and End Sub. With Range("A1:S100").Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Ar = ActiveCell.Row With Range(Cells(Ar, "a"), Cells(Ar, "s")).Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent3 .TintAndShade = 0.799981688894314 .PatternTintAndShade = 0 End With 5. Save file and open. Sorry for the confusion. "h2fcell" wrote: If what your looking for is highlighting that moves. Copy the below into the "ThisWorkbook" Object. Save file and open. I did not include the IF THEN to test for "Pending" in column E. Private WithEvents App As Application Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) With Range("A1:S100").Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Ar = ActiveCell.Row With Range(Cells(Ar, "a"), Cells(Ar, "s")).Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent3 .TintAndShade = 0.799981688894314 .PatternTintAndShade = 0 End With End Sub Private Sub Workbook_Open() Set App = Application End Sub "exor" wrote: Hi all. I need help with some programing. I hope i explain this right. Also if whom ever helps me please use remarks so I can learn what you did. ok here goes: i let me set this up. these are the name of some of the collums: a b d e f g-s row | app id | name | agent | lease approval | com Paid | ....... | --------------------------------------------------------------------------------- 1 1234 joe john A+, A thru E Yes or No varys or PENDING what i need to do is change the color of the row i have the active cell in. i want that row to change from collum a thru s in the active cell row. it has to have the color change to light green (green for money hehehe) one thing though i really need it to not change any other row but the one with the active cell. so the commpaid can be changed in any row and only that row will be changed. ok here is another tidbit. it also has to check if collum e has letters in it or PENDING if pending then must be error and no change happens. i had an earlier post on this newgroup and got an answer not exact what i wanted so i tried to change it but unsucessful at it. pob my fualt i didnt explain it right so i am trying again. Please use rem statments so i can learn how to change it in case i didnt explain right so i can try and mod it. thanks oh here is the other sub that was writen maybe it will help also Sub ColorCells() Dim cell As Range For Each cell In Selection With Range(cell, cell.Offset(0, 18)).Interior Select Case cell Case "pending" .ColorIndex = 6 Case "yes" .ColorIndex = 2 Case "e" .ColorIndex = 3 Case Is < "pending" .ColorIndex = 50 End Select End With Next cell End Sub i tried to use this by creating a function to call it here is what i tried to do Function colorchangerow(commpaid, leasescore) 'this module will call three different subs to change the color of the rows 'th row will be changed as follow a thru d will be clear 'the pending row will be color 'the paid comision will be green ' commpaid is taken from the e collom and the leasegrade is taken from the f collum commpaid = "No" Application.Volatile If commpaid = "No" And leasescore = "PENDING" Then 'this for when we just input in and are waiting for lease score Call ColorCells ElseIf commpaid = "No" And leasescore < "PENDING" Then 'this for when the lease score is in Call ColorCell1 ElseIf commpaid = "Yes" And leasescore < "PENDING" Then ' this is for color change when commision are paid Call ColorCell2 End If End Function i hope this help sorry if it is not understandable. thanks again |
| All times are GMT +1. The time now is 10:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com