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.
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.
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
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
ASKER
Hi Kevin.
Thanks for such a quick reply.
Hmmmm - now they have all gone white in C1:AO1
Thanks for such a quick reply.
Hmmmm - now they have all gone white in C1:AO1
Try the code in the second post.
Kevin
Kevin
ASKER
same
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I was trying to work this earlier with Conditional Formatting. Would CF be an easier approach?
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
Kevin
ASKER
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.
That last one works perfectly.
When you have no more use for your brain - mail it to me!!!
Thanks so much once again.
Nick.
>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
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
ASKER
Sorry Kevin - I meant to incorporate it into some code so I can use a switchable button to turn the highlighting on and off.
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
=AND(LEN(C3)=0,$A$1="Yes")
Kevin
ASKER
Are there any advantages in one over the other?
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
- 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
ASKER
Thanks Kevin (dont forget - when you've finished with it ........)
PAQ with "A"
PAQ with "A"
ASKER
Also have no idea how this ended up in windows TA... ??????
>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
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
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