VBA - change row font color if value of cell is

[MZeee]

Hello Board!

I have been conditionally formatting my spreadsheet twice a day (I download new data every 4 hours) to do the same thing, but I think it would save me a lot of time to have code that does this. The amount of rows varies, but the columns are static (always A-Q)

How can you:

-change the font color to green of the entire row (starting at row 5 and does to all rows) if the value in column O says "Picked". Bold the entire row.

-change the font color to orange of the entire row (starting at row 5 and does to all rows) if the value in column O says "Picked Partial". Bold the entire row.

-in column M (starting at row 5 and does all rows) create formula of H5-I5....if the resulting value is less than 0, change cell to red fill. Delete all values in column M that are greater than or equal to 0.

Thank you for the help.

- MZ

 

[JoeMo]

Can you clarify:
For col M, what does "delete the value" mean when the cell contains a formula? Do you simply want nothing showing in the cell [formula: IF(H5-I5>=0,"",H5-I5)], or you want to delete the cell, or you want to delete the formula?

For a row containing "Picked" or "Picked Partial" in col O and a value less than 0 in col M, do you want the red fill in M to override the row fill?

Does entire row mean the entire row or just the A:Q cells?

 

[MZeee]

Hey JoeMo,

Thanks for looking into this.

What I mean by "delete the values" is really just clear the contents of the cells if, after running the formula, it is 0 or above. So yes, basically deleting the formula and leaving nothing in the cell.

For the red fill... I would like for the red fill in Col M to override the green font color in those instances where they both occur.

Entire row would mean just between A:Q.

Thanks JoeMo

 

[JoeMo]

Since you download new data every 4 hours, I'll assume you really don't care if all the formulas are removed from M5:M & lastrow in range A5:Q & lastrow.

See if this does what you want:

Sub MZeee()
Dim lR As Long, c As Range
lR = ActiveSheet.Range("A:Q").Find(What:="*", searchorder:=xlByRows, SearchDirection:=xlPrevious).Row
Application.ScreenUpdating = False
Range("A5:Q" & lR).Interior.Color = xlNone
For Each c In Range("O5:O" & lR).Cells
    Select Case c.Value
        Case "Picked": Range(Range("A" & c.Row), Range("Q" & c.Row)).Interior.Color = vbGreen
        Case "Picked Partial": Range(Range("A" & c.Row), Range("Q" & c.Row)).Interior.Color = RGB(251, 169, 5)
    End Select
Next c
With Range("M5:M" & lR)
    .Formula = "=H5-I5"
    .Calculate
    .Value = .Value
    For Each c In .Cells
        If c.Value >= 0 Then
            c.ClearContents
        Else
            c.Interior.Color = vbRed
        End If
    Next c
End With
Application.ScreenUpdating = True
End Sub

 

[MZeee]

Hey JoeMo,

Thanks for giving this a shot.

I used the code provided. It does calculate the difference between H and I, but the red fill does not show.

The lines that have "Picked Partial" in column O does work, however it is orange fill and not orange font.

Also, the lines that are fulled "Picked" in column O does not show the green/bold font. No change occurred after the macro.

Finally, after executing the macro, it removed ALL other line fill colors on the entire page (with the exception of the picked partial orange rows).

-MZeee