Cell shading depending on value 2 cells below in a row.

Hi Experts
I am using this code:


Sub ColourNonOrder()
    Dim rng As Range, cell As Range
    Set rng = Range("C1:AO1")
    For Each cell In rng
    If cell.Offset(0, 2).Value = "#N/A"  Then
       cell.Interior.Color = vbGreen
    Else
       cell.Interior.Color = xlAutomatic
    End If
  Next
End Sub


to try and do the following:

Look at row 3
(in row 3 there is a IF,ISNA,Lookup and I'm trying to return a green shading (in Row 1) above any cell that has nothing returned).
Instead - I'm getting all of row 1 from C to AO changing to green.

How can I adapt this please?
Ultimately I am trying to add a macro button to turn on or off cell shading in row 1.

Many thanks

Nick.
LVL 13
Nick DennyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
Sub ColourNonOrder()
    Dim rng As Range, cell As Range
    Set rng = Range("C1:AO1")
    For Each cell In rng
    If cell.Offset(2, 0).Value = "#N/A"  Then
       cell.Interior.Color = vbGreen
    Else
       cell.Interior.Color = xlAutomatic
    End If
  Next
End Sub

Kevin
zorvek (Kevin Jones)ConsultantCommented:
Another change:

Sub ColourNonOrder()
    Dim rng As Range, cell As Range
    Set rng = Range("C1:AO1")
    For Each cell In rng
    If IsError(cell.Offset(2, 0)) Then
       cell.Interior.Color = vbGreen
    Else
       cell.Interior.Color = xlAutomatic
    End If
  Next
End Sub

Kevin
Nick DennyAuthor Commented:
Hi Kevin.
Thanks for such a quick reply.
Hmmmm - now they have all gone white in C1:AO1
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

zorvek (Kevin Jones)ConsultantCommented:
Try the code in the second post.

Kevin
Nick DennyAuthor Commented:
same
zorvek (Kevin Jones)ConsultantCommented:
If the result of the formula in row 3 is to return an empty string then try:

Sub ColourNonOrder()
    Dim rng As Range, cell As Range
    Set rng = Range("C1:AO1")
    For Each cell In rng
    If Len(cell.Offset(2, 0)) = 0 Then
       cell.Interior.Color = vbGreen
    Else
       cell.Interior.Color = xlAutomatic
    End If
  Next
End Sub

Kevin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nick DennyAuthor Commented:
I was trying to work this earlier with Conditional Formatting. Would CF be an easier approach?
zorvek (Kevin Jones)ConsultantCommented:
It would be cleaner but let's work out the "what is in row 3" issue first - then well build the conditional formatting formula.

Kevin
Nick DennyAuthor Commented:
Peachy.
That last one works perfectly.
When you have no more use for your brain - mail it to me!!!
Thanks so much once again.
Nick.
zorvek (Kevin Jones)ConsultantCommented:
>When you have no more use for your brain - mail it to me!!!
This is a new one! ;-)

To use conditional formatting, select cells C1:AO1 and choose Format->Conditional Formatting. Set the drop down to "Formula is", enter this formula:

=LEN(C3)=0

and set the format to green. Click OK and your done!

Kevin
Nick DennyAuthor Commented:
Sorry Kevin - I meant to incorporate it into some code so I can use a switchable button to turn the highlighting on and off.
zorvek (Kevin Jones)ConsultantCommented:
You can still use conditional formatting. Just set aside a cell to do the switching. Say A1 with a validation list: Yes, No. Then the conditional formatting formula would be:

=AND(LEN(C3)=0,$A$1="Yes")

Kevin
Nick DennyAuthor Commented:
Are there any advantages in one over the other?
zorvek (Kevin Jones)ConsultantCommented:
From a UI perspective? Not really. From a development perspective? Up to you.

- The conditional formatting solution requires less "code".

- The conditional formatting solution does not require that macros be enabled (of no help if there is other VBA code you need).

- Conditional formatting is less understood by Excel developers than VBA code.

Kevin
Nick DennyAuthor Commented:
Thanks Kevin (dont forget - when you've finished with it ........)
PAQ with "A"
Nick DennyAuthor Commented:
Also have no idea how this ended up in windows TA... ??????
zorvek (Kevin Jones)ConsultantCommented:
>Also have no idea how this ended up in windows TA... ??????
No worries - I'll fix it.

With regard to the comment above:

- Conditional formatting is less understood by Excel developers than VBA code.

I need to restate as:

- I, personally, find conditional formatting to be a little more obtuse than straight VBA code.

Kevin
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.