Excel VBA Range and Values not goin IN?

[Robbo]

Any one help

trying to get this to work can change the fon and cell colour but cant put a value in the CELL
the C B A and AA

everytime have in the code they move it back to the top so only does the first cell P12-Q12

Public Sub Worksheet_Change(ByVal Target As Range)
Set MyTotal = Range("P12:P391")

For Each Cell In MyTotal
        If Cell.Value < 40 Then
            Cell.Offset(0, 1).Interior.ColorIndex = 5 'BLUE
            Cell.Offset(0, 1).Font.ColorIndex = 2 'BLACK
            'cell.Offset(0, 1).Value = "C"
        End If
        If Cell.Value > 40 Then
            Cell.Offset(0, 1).Interior.ColorIndex = 10 'GREEN
            Cell.Offset(0, 1).Font.ColorIndex = 2 'BLACK
            'cell.Offset(0, 1).Value = "B"
        End If
        If Cell.Value > 80 Then
            Cell.Offset(0, 1).Interior.ColorIndex = 6 'YELLOW
            Cell.Offset(0, 1).Font.ColorIndex = 0 'WHITE
            'cell.Offset(0, 1).Value = "A"
        End If
        If Cell.Value > 120 Then
            Cell.Offset(0, 1).Interior.ColorIndex = 3 'RED
            Cell.Offset(0, 1).Font.ColorIndex = 2 'BLACK
           ' cell.Offset(0, 1).Value = "AA"
        End If
        If Cell.Value = 0 Then
            'Cell.Offset(0, 1).Select
            Cell.Offset(0, 1).Interior.ColorIndex = xlNone ' WHITE
            Cell.Offset(0, 1).Font.ColorIndex = 0 'WHITE
        End If
Next
End Sub

any help ?

[si_the_geek]

Thread moved from the 'VB6' forum to the 'Office Development/VBA' forum

[vbfbryce]

If you want to tie the code to the Worksheet_Change event, you should only execute code necessary for the "target" cell(s). Or do you really need to analyze 380 or so cells each time any change is made?

[Robbo]

If you want to tie the code to the Worksheet_Change event, you should only execute code necessary for the "target" cell(s). Or do you really need to analyze 380 or so cells each time any change is made?

hiya wud i be best doing this by cell then?

whats esyest way to ammed the code its been a long day

[vbfbryce]

Public Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
    Select Case Target.Value
        Case 0
            Cells(Target.Row, Target.Column + 1).Interior.ColorIndex = xlNone
            Cells(Target.Row, Target.Column + 1).Font.ColorIndex = 0
        Case Is < 40
            Cells(Target.Row, Target.Column + 1).Interior.ColorIndex = 5
            Cells(Target.Row, Target.Column + 1).Font.ColorIndex = 2
            Cells(Target.Row, Target.Column + 1).Value = "C"
        Case Is < 81
            Cells(Target.Row, Target.Column + 1).Interior.ColorIndex = 10
            Cells(Target.Row, Target.Column + 1).Font.ColorIndex = 2
            Cells(Target.Row, Target.Column + 1).Value = "B"
        Case Is < 121
            Cells(Target.Row, Target.Column + 1).Interior.ColorIndex = 6
            Cells(Target.Row, Target.Column + 1).Font.ColorIndex = 0
            Cells(Target.Row, Target.Column + 1).Value = "A"
        Case Else
            Cells(Target.Row, Target.Column + 1).Interior.ColorIndex = 3
            Cells(Target.Row, Target.Column + 1).Font.ColorIndex = 2
            Cells(Target.Row, Target.Column + 1).Value = "AA"
    End Select
    Else
    'don't do anything
    End If
End Sub

The first thing this will check for is whether a change was made to a cell in column A (1). If so, it will go through the Select statement and act based on the value in the changed cell only.

[Robbo]

ok well the changes are in colums 6 to 15 the total i want to check is in 16 (value) and the formatiing of color index is in 17

this doesnt quite work correctly

[vbfbryce]

Yes, it was sample code. You'd have to change the references to meet your specific need. For example, the first line that checks where the change was made could be changed to this:

If Target.Column > 5 And Target.Column < 16 Then

[Robbo]

problem is now im checking the range, which i did on first example
then have to select the cell i want to check so target is selectiong the correct number?

so depending where i click it will do the next colum to chekc number then next column with the formating, so get C C C C depending how far to the left have entered?

[vbfbryce]

I'm not sure I follow that, but, if what you want is that any time you change a value in columns 6 to 15, the cell in the same row in column 16 gets updated, that can easily be done.

target.row tells you which row to update, target.cell would tell you how far to the right you'd need to offset (to get to column 16).

Make sense?

[Robbo]

hiya cheers for your help got this to work

Public Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column >= 6 And Target.Column < 16 Then
    Select Case Cells(Target.Row, 16).Value
        Case Is = 0
            Cells(Target.Row, 17).Interior.ColorIndex = xlNone
            Cells(Target.Row, 17).Font.ColorIndex = 0
            Cells(Target.Row, 17).Value = Null
        Case Is < 40
            Cells(Target.Row, 17).Interior.ColorIndex = 5 'BLUE
            Cells(Target.Row, 17).Font.ColorIndex = 2 'BLACK
            Cells(Target.Row, 17).Value = "C"
        Case Is < 81
            Cells(Target.Row, 17).Interior.ColorIndex = 10 'GREEN
            Cells(Target.Row, 17).Font.ColorIndex = 2 'BLACK
            Cells(Target.Row, 17).Value = "B"
        Case Is < 121
            Cells(Target.Row, 17).Interior.ColorIndex = 6 'YELLOW
            Cells(Target.Row, 17).Font.ColorIndex = 0 'WHITE
            Cells(Target.Row, 17).Value = "A"
        Case Else
            Cells(Target.Row, 17).Interior.ColorIndex = 3 'RED
            Cells(Target.Row, 17).Font.ColorIndex = 2 'BLACK
            Cells(Target.Row, 17).Value = "AA"
    End Select
    Else
    'don't do anything
    End If
End Sub

just have to watch i dont insert any rows

cheers