Help to check value, if one of value <>0 then that row mark red

[sbv1986]

Hi all:
I have data in sheets(data), I want macro to do that:
1. Minus value Column(D,E,F,G,H,I) with column(M,N,0,P,Q,R) like this:
Si = Di - Mi
Ti = Ei - Ni
Ui = Fi - Oi
Vi = Gi - Pi
Wi = Hi - Qi
Xi = Ii - Ri
2. Check value row i = 1 to last row that:
if Si<>0 or Ti<>0 or Ui<>0 or Vi<>0 or Wi<>0 or Xi<>0 then row(i) mark red font.

Thanks.

 

[bobsan42]

I wouldn't use a macro, but Conditional formatting with a formula like this:

=OR([COLOR=#333333]S1<>0,T1<>0,U1<>0,V1<>0,W1<>0,X1<>0)[/COLOR]

or this:

=[COLOR=#333333]S1*T1*U1*V1*W1*X1<>0[/COLOR]

to simplify you can use the formula above in column Z like this:

Zi =[COLOR=#333333]Si*Ti*Ui*Vi*Wi*Xi<>0[/COLOR]

Then use a formula like this for conditional formatting:

=Z1

 

[Rick Rothstein]

or this:

=[COLOR=#333333]S1*T1*U1*V1*W1*X1<>0[/COLOR]

I think you would want to add those values for the test, not multiply them (the OP wants to react if any one or more are not 0).

 

[bobsan42]

Good point, Rick - my mistake. Thanks for pointing it out.

Still if adding is used - it has to be combined with ABS() otherwise the result can still be zero even if the numbers are not.

 

[Rick Rothstein]

Good point, Rick - my mistake. Thanks for pointing it out.

Still if adding is used - it has to be combined with ABS() otherwise the result can still be zero even if the numbers are not.

Good point back at you. At a little after 2:30am (just before going to bed), that suggestion was the best I could come up with. Now that I am fully awake, I notice the cells are contiguous which means, for Conditional Formatting, selecting Row 1 and then using this formula should work...

=COUNT($S1:$X1)

 

[sbv1986]

Thanks both all but I want to do this by macro. Because I have another code to get data to column(A:R), code will clear all data in sheets(data) so can't do by fomula at one

 

[bobsan42]

If the macro doesn't clear formatting and conditional formatting the above suggestions should work (really depending on what the macro does).

Anyway, you can use something like this code:

Sub cba()
    On Error Resume Next
    Dim wsh As Worksheet: Set wsh = ActiveWorkbook.Worksheets("Data")
    Dim rngS As Range: Set rng = Intersect(wsh.UsedRange, wsh.Range("S:S"))
    Dim cc As Range
    Dim i As Long
    
    If rngS Is Nothing Then GoTo EP
    
    For Each cc In rngS
        i = 0
        Do While i < 6
            If cc.Offset(0, i).Value <> 0 Then
                cc.EntireRow.Font.Color = vbRed
                i = 10
            Else
                i = i + 1
            End If
        Loop
    Next cc
    
EP:
    On Error Resume Next
    Set wsh = Nothing
    Set rngS = Nothing
    Set cc = Nothing
End Sub

 

[Rick Rothstein]

Thanks both all but I want to do this by macro. Because I have another code to get data to column(A:R), code will clear all data in sheets(data) so can't do by fomula at one

Assuming you are doing this for the exact range S1:X1 only, then including this line of code in your macro (at the appropriate place) will color Row 1 red if any one or more cells in the range S1:X1 has a non-zero value in it...

If [COUNT(S1:X1)] Then Rows(1).Interior.Color = vbRed