need help with a bit of proggraming

[exor]

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

 

[Joel]

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)

 

[Don Guillett]

Right click sheet tab>view code>copy/paste this>change range to suit>SAVE
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

 

[exor]

Right click sheet tab>view code>copy/paste this>change range to suit>SAVE
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


















- 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

 

[Don Guillett]

Why not just try it as instructed?
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Right click sheet tab>view code>copy/paste this>change range to suit>SAVE
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


















- 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

 

[h2fcell]

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"?

 

[h2fcell]

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

 

[h2fcell]

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.