Adapted loop causing "Compile Error"

[johnnyb5]

I've researched a loop from a 2009 question and tried to adapt it to select my cells in columns E & F that are greater than zero and highlight them green (if they are 0 then do nothing). Those columns have formulas that are returning values as appropriate without errors. When I tried to adapt the loop I got a compile error that I don't understand how to fix. The error states that "for each control variable must be variant or object" and it highlights the first "c" in the second row. The loop's adapted instructions are:

'Format GYR in table

i = 6 'start row number
For Each c In ActiveSheet.Range("e6:f" & Range("f" & Rows.Count).End(xlUp).Row)
If Cells(i, 5) > 0 Then
Rng = "e" & i & ":" & "f" & i
Range(Rng).Interior.ColorIndex = 4 'green
'If Cells(i, 4) = "" Then
' Cells(i, 4).Interior.ColorIndex = 3 'red
Else
End If
End If
i = i + 1
Next c

Can anyone help with working out the loop errors so that my cells that are greater than zero in column E & F can get colored green?

 

[Trebor76]

Do both columns have to be greater than zero to shade both green or can it be either column E or column F?

 

[starl]

Indenting code properly is VERY important. In this case, you have an extra End If. Delete either one of them.

Tho, noticing you have commented code out, I would probably comment out the first end if. And the Else above it

 

[hiker95]

johnnyb5,

If I understand you correctly, here is another macro solution for you to consider.

Sample raw data:

Excel 2007
	D	E	F
6		1	2
7		3	4
8		5	6
9		0	0
10
11		7	8
12		8	9
13
Sheet1

After the macro:

Excel 2007
	D	E	F
6		1	2
7		3	4
8		5	6
9		0	0
10
11		7	8
12		8	9
13
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub FormatGYR_in_table()
' hiker95, 09/16/2015, ME887326
Dim c As Range
Application.ScreenUpdating = False
For Each c In ActiveSheet.Range("E6:E" & Range("F" & Rows.Count).End(xlUp).Row)
  If c.Value > 0 Then c.Interior.ColorIndex = 4 'green
  If c.Offset(, 1).Value > 0 Then c.Offset(, 1).Interior.ColorIndex = 4 'green
  If c.Offset(, -1) = vbEmpty Then c.Offset(, -1).Interior.ColorIndex = 3 'red
Next c
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the FormatGYR_in_table macro.

 

[johnnyb5]

Hiker 95,

Thanks so much for the improved code. I tried endless corrections and edits but I could not solve the "Compile" problem. Even when I tried to use your improved code I still got the "Compile" error however, I was able to simply switch the "C" in your code with a "Z" in all the lines and then it worked like a charm without any errors. You hit the nail on the head and I certainly appreciate both your assistance and your patience!!! My macro is working fine now and coloring my lines just as intended.

Sincerely,
JB

 

[hiker95]

Hiker 95,

Thanks so much for the improved code. I tried endless corrections and edits but I could not solve the "Compile" problem. Even when I tried to use your improved code I still got the "Compile" error however, I was able to simply switch the "C" in your code with a "Z" in all the lines and then it worked like a charm without any errors. You hit the nail on the head and I certainly appreciate both your assistance and your patience!!! My macro is working fine now and coloring my lines just as intended.

Sincerely,
JB

johnnyb5,

Thanks for the feedback.

You are very welcome.

Glad I could help you, so that you could find a solution that worked correctly.

And, come back anytime.

 

[mole999]

Disregard, so late to the party