CODEWITHSUNDEEP
vbaexcel
user3776800
user3776800

Reputation: 57

Excel VBA changing color on cell at the time

Maybe due to my poor English some of you may misunderstood what I wanted to do. Anyways, here is the code that I wanted. After many trial and errors I made it but I doubt its the best way but it works =). If anyone know how to make the code better please tell me =). I previous post is also below this new code.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim row As Integer

For row = 1 To 1000
If Cells(row, "A").Value = "Yes" Then
    Range("B" & row).Interior.ColorIndex = 4


    End If

    If Cells(row, "A").Value = "yes" Then
    Range("B" & row).Interior.ColorIndex = 4


    End If


Next row

End Sub

Previous post

I have written a VBA code in excel where I want to change a cell color everytime I write "yes". In this case I write "yes" in column E and column A should change color. I have used a for loop but it does not run as I want. I have a feeling I am thinking a bit wrong...

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cellNr As Long

For cellNr = 1 To 5

    If Range("E" & cellNr).Value = "yes" Then

       Range("A" & cellNr).Interior.ColorIndex = 5   

       Else
          ActiveCell(0, -2).Interior.ColorIndex = 4
       End If

Next cellNr

End Sub

Upvotes: 0

Views: 1636

Answers (3)

Mark Balhoff
Mark Balhoff

Reputation: 2356

If I understand what you are trying to do with the following line:

ActiveCell(0, -2).Interior.ColorIndex = 4

Replace it with:

ActiveCell.Offset(0, -2).Interior.ColorIndex = 4

As far as I know, the line I told you to remove will always throw an error. It doesn't make any sense syntactically. You should use the Offset function to get positions relative to the Active Cell. But also, this offset function will throw an error if your active cell is in column A or B because there are less than 2 columns to the left of the active cell.

Upvotes: 1

teylyn
teylyn

Reputation: 35915

Use conditional formatting with a formula. Starting in row 1 the formula required is

=$E1="yes"

enter image description here

Upvotes: 3

Gary's Student
Gary's Student

Reputation: 96753

Consider:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rOfInterest As Range
    Set rOfInterest = Range("E1:E5")
    If Intersect(Target, rOfInterest) Is Nothing Then Exit Sub
    v = Target.Value
    If v = "yes" Then
        Target.Offset(0, -4).Interior.ColorIndex = 5
    Else
        Target.Offset(0, -4).Interior.ColorIndex = 4
    End If
End Sub

and make sure the macro is in the worksheet code area.

Upvotes: 0

Related Questions