PDA

View Full Version : Expand the Range For Each Cell in Range



Loss1003
03-14-2016, 07:59 AM
Please let me modify the below code to work for range M2:M8000 in addition to L2:L8000

if i adjust the set range to Range("L2:M8000") from Range("L2:L8000")the code runs at each cell .


Sub interior2()
Sheet2.Activate

Dim rngMyRange As Range
Set rngMyRange = Range("L2:L8000")

For Each rngMyRange In rngMyRange

If rngMyRange(1, 2) = "" And rngMyRange(1, 4) = "" And rngMyRange <= Date Then
'=AND(M2="",O2="",L2<=TODAY())
rngMyRange.Interior.Color = RGB(99, 37, 35) 'Dark Red
rngMyRange.Font.Color = RGB(255, 255, 255) 'Dark Red
rngMyRange.Font.Bold = True
End If

Next rngMyRange
End Sub

Bob Phillips
03-14-2016, 11:02 AM
if i adjust the set range to Range("L2:M8000") from Range("L2:L8000")the code runs at each cell .

Isn't that what you want?

Paul_Hossler
03-14-2016, 11:10 AM
Sub interior2()
Dim rngMyRange As Range, rngMyCell as Range

Sheet2.Activate

Set rngMyRange = Range("L2:M8000")

For Each rngMyCell In rngMyRange.Cells '<<<<<<<<<<<<<<<<<<<<<<

'from here on, I don't know what you want to do.
'but rngMyCell will go through all 15,998 cells in L2:M8000
'as is, rngMyRange(1, 2) will ALWAYS be M2 and rngMyRange(1, 4) will ALWAYS be O2
'and all 15,998 cells will get the formatting since you're applying it to rngMyRange = L2:M8000


If rngMyRange(1, 2) = "" And rngMyRange(1, 4) = "" And rngMyRange <= Date Then
'=AND(M2="",O2="",L2<=TODAY())
rngMyRange.Interior.Color = RGB(99, 37, 35) 'Dark Red
rngMyRange.Font.Color = RGB(255, 255, 255) 'Dark Red
rngMyRange.Font.Bold = True
End If

Next rngMyCell
End Sub



so maybe some more details will help

Loss1003
03-14-2016, 11:49 AM
The code works, however when I further expand the range past (L2:L8000) the code runs very slow.
for instance if I run (L2:L2000) the codes takes a lot longer to process. anyway to speed things up w/o using conditional formatting.


Sub interior2()
Sheet2.Activate

Dim rngMyRange As Range
Set rngMyRange = Range("L2:L8000")

For Each rngMyRange In rngMyRange

If rngMyRange(1, 2) = "" And rngMyRange(1, 4) = "" And rngMyRange <= Date Then
'=AND(M2="",O2="",L2<=TODAY())
rngMyRange.Interior.Color = RGB(99, 37, 35) 'Dark Red
rngMyRange.Font.Color = RGB(255, 255, 255) 'Dark Red
rngMyRange.Font.Bold = True
End If

Next rngMyRange
End Sub