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

sbv1986

Board Regular
Joined
Nov 2, 2017
Messages
87
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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
I wouldn't use a macro, but Conditional formatting with a formula like this:
Code:
=OR([COLOR=#333333]S1<>0,T1<>0,U1<>0,V1<>0,W1<>0,X1<>0)[/COLOR]
or this:
Code:
=[COLOR=#333333]S1*T1*U1*V1*W1*X1<>0[/COLOR]

to simplify you can use the formula above in column Z like this:
Code:
Zi =[COLOR=#333333]Si*Ti*Ui*Vi*Wi*Xi<>0[/COLOR]
Then use a formula like this for conditional formatting:
Code:
=Z1
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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:
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
 
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top