0

This is a continuation for the following question: What is the cause for Conditional Formatting to get jumbled up?

In an attempt to prevent my conditional formatting from going haywire, I decided to convert it into code in VBA. I decided to start small and start with converting one conditional formatting into VBA.

Explanation:

In column O there are a series of numbers, obtained from a different sheet. User inputs number in column F. For example if number in F9 is less than O9, the font colour will become red. If not number remains normal. The formula should start at row 9 and can continue down onwards and should be automatic. Meaning the moment a number is keyed in column F the font colour should change instantly.

The following is the code I created so far:

Sub change_color()

With Me.Range("f9", Range("f" & Rows.Count).End(xlUp)) 'so the formula will carry onwards from f9 onwards
    If f9 < o9 Then
        Range(f).Font.Color = vbRed
    End If
End With

End Sub

But alas it didn't work. I also tried linking it to a button and nothing happens. And I also remember to remove my old conditional formatting as well. Is there something I'm missing?

Improve this question

2 Answers 2

Reset to default
1

You are after something like the code below.

This code is to be ran once, it will lopp through the entire column "F" in your worksheet, and change the font of all instances.

Regular Module Code

Option Explicit

Sub change_color()

Dim LastRow As Long, i As Long

With Worksheets("Sheet1") ' modify to your sheet's name
    LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row

    For i = 1 To LastRow 
        If .Range("F" & i).Value < .Range("O" & i).Value Then
            .Range("F" & i).Font.Color = vbRed
        Else
            .Range("F" & i).Font.Color = vbBlack
        End If
    Next i        
End With

End Sub

To "catch" the modification in real-time, when someone changes a value in column "F", and then change the font according to the criteria you specified, you need add the following code to the Worksheet module, where you have your data, and add the piece of code below to Worksheet_Change event.

Code in Sheet1 module (modify to your sheet's)

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 6 Then ' if someone changes a value in column "F"
    Application.EnableEvents = False

    If Target.Value < Range("O" & Target.Row).Value Then
        Target.Font.Color = vbRed
    Else
        Target.Font.Color = vbBlack
    End If

End If
Application.EnableEvents = True

End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Sweet. Works like a charm. I had to use the regular module code since the other one doesn't seem to work for me. I already have a worksheet_change module in my code so I just used call function to execute the task. Thanks a lot. Now to repeat this 11 more times...
0

Does this work for you?

Option explicit
Sub ChangeColor()

With thisworkbook.worksheets(YOURSHEETNAME) 'Replace with sheet name as per your workbook.'

Dim LastRow as long
Lastrow = .cells(.rows.count,"F").end(xlup).row

Dim RowIndex as long

For rowindex = 9 to LastRow

If .cells(rowindex,"F").value2 < .cells(rowindex,"O").value2 then
.cells(rowindex,"F").font.color = vbred
End if

Next rowindex

End With 

End Sub
Improve this answer

4 Comments

there is no use of Me inside a worksheet module, it automaticaly refers to that worksheet as ActiveSheet, you can remove the With Me and it work the same
Written on mobile, untested. I did not get rid of the With, only changed the object it returns, so I think all the . should be fine.
Where is it you think the . prefix is wrongfully placed here? I see no obvious errors here? @ShaiRado
@eirikdaude you're right, comment removed, I missed the line he replaced of With thisworkbook.worksheets(YOURSHEETNAME)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.