If and offset range help

twinpowr

Board Regular
Joined
Mar 14, 2007
Messages
73
Hi, here is the problem i am having

I have a worksheet which i need to check the condition of cell lets says k8=7 , if condition is true change the range of cells in this case A8:I8 to interior color = ? and font color = ? I then need to move 1 row down and perform the check again. k9=7, change the color of range A9:I9

I then need to add on if k8>7 then change interior and font color to a another color.

I can get it to check the first cell change the interior and font color as i need, but i cant get it to move down the required number of rows. in this case check cell value from i8 to i90 and change a8:i8 down a90:i90.

I have tried conditional formatting, but this does not seem to work.

here is some of the code i have tried, probably a million miles away

Sub check_dates2()

Range("a8").Activate
c = 1

Do While c <= 90

If Range("k8") = 7 Then

Range(ActiveCell, ActiveCell.Offset(0, 8)).Interior.ColorIndex = 3
Range(ActiveCell, ActiveCell.Offset(0, 8)).Font.ColorIndex = 36

ActiveCell.Offset(1, 0).Select

c = c + 1
End If

Loop

End Sub

any help would be appreciated.

Ally.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Ally

Go up the range rather than down it. I have made the modification to your code - but these are untested !!!

Code:
Sub check_dates2()

Range("a90").Activate
c = 90

Do While c  >8 

If Range("k8") = 7 Then

Range(ActiveCell, ActiveCell.Offset(0, 8)).Interior.ColorIndex = 3
Range(ActiveCell, ActiveCell.Offset(0, 8)).Font.ColorIndex = 36

ActiveCell.Offset(1, 0).Select

c = c - 1
End If

Loop

End Sub

regards

Kaps
 
Upvote 0
Thanks, that seems to have done the trick.

had no idea it would be easier to go backwards rather than forwards.

many thanks

ally
 
Upvote 0
thanks for the previous solution

i need the code to do a little bit more

Sub check_dates2()

Range("a90").Activate
c = 90

Do While c > 7

If Range("k8") > 7 Then

Range(ActiveCell, ActiveCell.Offset(0, 8)).Interior.ColorIndex = 3
Range(ActiveCell, ActiveCell.Offset(0, 8)).Font.ColorIndex = 36

Else

Range(ActiveCell, ActiveCell.Offset(0, 8)).Interior.ColorIndex = 4
Range(ActiveCell, ActiveCell.Offset(0, 8)).Font.ColorIndex = 31

ActiveCell.Offset(-1, 0).Select

c = c - 1
End If

Loop

End Sub

when i have added the second set of conditions, it sends the code into an infinite loop.
the code needs to check each line in order. So check cell k90 for the condition, then k34,k33 and change the range a90:i90 up to the top a8:i8 applying the correct interior and font color.

any more ideas from anyone would be appreciated.

Ally
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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