VBA Activate Cell = color change

[Pinaceous]

Hi All,

I'm working with a column H11:H180; where if a value is entered into this column, I'd like the adjacent cell to turn red until a value is entered into that cell.

For example, if I enter a value into cell H11, the cell I11 will turn red until a value is entered here.

Likewise, if I enter in a value into cell H12, the cell I12 will turn red until a value is entered here and so on until cell H180 of that column.

Can you help me figure this out?

Thank you!
pinaceous

 

[kweaver]

Does this do it if you select I11:I180 and use this rule? It's conditional formatting rather than VBA.

 

[rlv01]

If I understand correctly, then maybe something like this

Sub AdjCellRed()
    Dim WS As Worksheet
    Dim CellRange As Range
    Dim FStr As String

    Set WS = ActiveSheet

    Set CellRange = WS.Range("H11:H180").Offset(0, 1)

    With CellRange
        '=AND(H11<>"", I11 ="")
        FStr = "=AND(" & .Range("A1").Offset(0, -1).Address(False, False) & "<>" & """" & """" & ", " & .Range("A1").Address(False, False) & " =" & """" & """" & ")"
        With .FormatConditions
            .Delete
            With .Add(Type:=xlExpression, Formula1:=FStr)   
                .Interior.Color = vbRed
            End With
        End With
    End With
End Sub

Kind of the same as what kweaver proposed, except that I'm using VBA to set up the conditional formatting.

 

[Pinaceous]

rlv01,

In using your code am I still using A1 written in your code?

Thanks!
-p

 

[Pinaceous]

Hi [U]kweaver,[/U]

In using your code I was getting a red cell for cell H11 if cell I11 is blank.

But I need you to change your formula to produce a red cell for cell I11, if it is blank, when I type a value into cell H11.

I tried to play with your formula but I need help.

Thank you!
-p

 

[rlv01]

rlv01,

In using your code am I still using A1 written in your code?

Thanks!
-p

Yes.

 

[Peter_SSs]

Hi @kweaver,

In using your code I was getting a red cell for cell H11 if cell I11 is blank.

I don't think you read @kweaver's instructions carefully as there should be no conditional formatting applied in column H at all.

select I11:I180 and use this rule?

 

[Pinaceous]

Hi Peter_SSs!

Many thanks for being a true MrExcel MVP, Moderator and for checking my mistake!

kweaver Sorry about that! Thanks for posting!

Thanks!
-pinaceous

 

[Pinaceous]

BTW,

How would I add an error code to that formula?

I'm under the impression that it would go something like this:

=IFERROR(AND(ISBLANK(I11),NOT(ISBLANK(H11))),"Error!")

But I'm getting no response.

Thank you!
-pin

 

[Peter_SSs]

Why do you want to add an error code. You are using this in conditional formatting where an error does not meet the conditions so the formatting is not applied. A CF formula is simply True (formatting is applied) or not True (= False or Error) and formatting is not applied. The formula does not return any text result to the cell, only formatting.

Can you explain in more detail exactly what you have and what you are trying to achieve?