PDA

View Full Version : Solved: color



asdzxc
06-07-2012, 03:42 AM
go to last item in B:,
if b8<b7, color red,
if b7<b6, color red.
plse supply code.

CatDaddy
06-07-2012, 08:23 AM
For i = Range("A" & rows.count).End(xlup).Row to 3 Step -1
if Range("B" & i).value > Range("B" & i-1).value Then
Range("B" & i).Interior.ColorIndex = 3
End if
Next i(

Opv
06-07-2012, 09:36 AM
go to last item in B:,
if b8<b7, color red,
if b7<b6, color red.
plse supply code.

Can you clarify your objective? Do you only want the last item to stay red until a new entry is added to column B and then to have it changed back to the default color or do you want the item to remain red as new entries are added? Regardless, Conditional Formatting could probably be adapted to serve your needs.

asdzxc
06-07-2012, 06:35 PM
Can you clarify your objective? Do you only want the last item to stay red until a new entry is added to column B and then to have it changed back to the default color or do you want the item to remain red as new entries are added? Regardless, Conditional Formatting could probably be adapted to serve your needs.

I want the item to remain red as new entries are added. I prefer
vba.

asdzxc
06-07-2012, 06:38 PM
For i = Range("A" & rows.count).End(xlup).Row to 3 Step -1
if Range("B" & i).value > Range("B" & i-1).value Then
Range("B" & i).Interior.ColorIndex = 3
End if
Next i(



if new entries are added, your vba is no longer working

Opv
06-07-2012, 08:53 PM
This seems a bit overkill but for what it's worth, you might try some variation of the following. This would need to be placed in the Sheet module for the worksheet that contains the data.


Private Sub Worksheet_Change(ByVal Target As Range)

Dim lRow As Integer, rng As Range

With Me

lRow = .Range("B65000").End(xlUp).Row

Set rng = .Range("B1:B" & lRow)

If Not Intersect(Target, rng) Is Nothing And _
Selection.Count = 1 Then

On Error Resume Next

If Target.Value < Target.Offset(-1, 0).Value And _
Target.Offset(-1, 0).Value < Target.Offset(-2, 0).Value Then

Target.Font.ColorIndex = 3

Else: Target.Font.ColorIndex = 0

End If

End If

On Error GoTo 0

End With

End Sub


Bear in mind that the above code will only work on new changes and additions. If you want it apply it to existing cells you will need to either re-enter the value(s) or copy and paste them individually so that the change event is triggered for the existing cells. The only way around that that I cam think of was to implement some sort of loop but that would be an even less efficient way to achieve your goal, as it would be looping through the same cells with each change and new entry. The code should work with no problem for ongoing new entries.

I'm sure there's probably a more efficient way to do what you want. Maybe someone with more experience than I will respond with a better solution. Good luck.

asdzxc
06-07-2012, 09:07 PM
Hi,Opv
Thank you for your code.
Conditional Formatting's formular re color, plse.

Aussiebear
06-08-2012, 12:17 AM
Don't be so lazy. Work it out yourself.

asdzxc
06-08-2012, 04:00 AM
proplem solved