ExcelBanter - Trouble getting the OFFSET fo work

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Trouble getting the OFFSET fo work (https://www.excelbanter.com/excel-programming/304734-trouble-getting-offset-fo-work.html)

Trouble getting the OFFSET fo work

To anyone,

I have a single column RANGE called "INITIALS" that is located 1
column to the left of a table of data who's RangeName is
"All_ASSIGNED_Schedules"

What I'm trying to do is find all cells in the data table containing
the value "4" and highlight the cell with color. (This part of the
code works fine) ...
but then, on the SAME row I wish to also highlight the column
containing the individuals INITIALS. When I step thru the code, I Know
I'm at the correct column because it reveals the correct set of
INITIALS, but I can't change the color of that cell?

Please advise what's wrong with the line containing the Comment: '???

Dim RowNum As Integer: Dim ColNum As Integer
Dim InitialsColumn As Integer: InitialsColumn =
Range("INITIALS").Column
Dim cell As Range: Range("All_ASSIGNED_Schedules").Select
For Each cell In Selection
' QUICK VIEW all 4-12 shifts with Lavender
If InStr(1, cell.Value, "4") Then
cell.Interior.ColorIndex = 39
RowNum = cell.Row: ColNum = cell.Column
ActiveCell.Offset(0, InitialsColumn - ColNum).ColorIndex =
39 '???
End If
Next cell

Any help would be appreciated!

Jim Pellechi

Trouble getting the OFFSET fo work

Didn't you forget the Interior?
ActiveCell.Offset(0, InitialsColumn -
ColNum).Interior.ColorIndex = 39

But also - why not just use conditional formatting to
accomplish this?

-----Original Message-----
To anyone,

I have a single column RANGE called "INITIALS" that is
located 1
column to the left of a table of data who's RangeName is
"All_ASSIGNED_Schedules"

What I'm trying to do is find all cells in the data table
containing
the value "4" and highlight the cell with color. (This
part of the
code works fine) ...
but then, on the SAME row I wish to also highlight the
column
containing the individuals INITIALS. When I step thru the
code, I Know
I'm at the correct column because it reveals the correct
set of
INITIALS, but I can't change the color of that cell?

Please advise what's wrong with the line containing the
Comment: '???

Dim RowNum As Integer: Dim ColNum As Integer
Dim InitialsColumn As Integer: InitialsColumn =
Range("INITIALS").Column
Dim cell As Range: Range
("All_ASSIGNED_Schedules").Select
For Each cell In Selection
' QUICK VIEW all 4-12 shifts with Lavender
If InStr(1, cell.Value, "4") Then
cell.Interior.ColorIndex = 39
RowNum = cell.Row: ColNum = cell.Column
ActiveCell.Offset(0, InitialsColumn -
ColNum).ColorIndex =
39 '???
End If
Next cell

Any help would be appreciated!

Jim Pellechi
.

Trouble getting the OFFSET fo work

You never change the activecell in your code and you never assign a value to
colNum (besides not using Interior as pointed out by others).

Dim RowNum As Integer: Dim ColNum As Integer
Dim InitialsColumn As Integer, cell As Range
Dim sh = Range("INITIALS").Parent
InitialsColumn =Range("INITIALS").Column
For Each cell In Range("All_ASSIGNED_Schedules").
' QUICK VIEW all 4-12 shifts with Lavender
If InStr(1, cell.Value, "4") Then
cell.Interior.ColorIndex = 39
sh.cells(cell.row, InitialsColumn - 1).Interior _
.ColorIndex = 39
End If
Next cell

If the column is adjacent to Table, then the above should work. If there is
a blank column in between, then change -1 to -2.

--
Regards,
Tom Ogilvy

"JimP" wrote in message
om...
To anyone,

I have a single column RANGE called "INITIALS" that is located 1
column to the left of a table of data who's RangeName is
"All_ASSIGNED_Schedules"

What I'm trying to do is find all cells in the data table containing
the value "4" and highlight the cell with color. (This part of the
code works fine) ...
but then, on the SAME row I wish to also highlight the column
containing the individuals INITIALS. When I step thru the code, I Know
I'm at the correct column because it reveals the correct set of
INITIALS, but I can't change the color of that cell?

Please advise what's wrong with the line containing the Comment: '???

Dim RowNum As Integer: Dim ColNum As Integer
Dim InitialsColumn As Integer: InitialsColumn =
Range("INITIALS").Column
Dim cell As Range: Range("All_ASSIGNED_Schedules").Select
For Each cell In Selection
' QUICK VIEW all 4-12 shifts with Lavender
If InStr(1, cell.Value, "4") Then
cell.Interior.ColorIndex = 39
RowNum = cell.Row: ColNum = cell.Column
ActiveCell.Offset(0, InitialsColumn - ColNum).ColorIndex =
39 '???
End If
Next cell

Any help would be appreciated!

Jim Pellechi

Trouble getting the OFFSET fo work

Don,

Thru your example I saw I left off "interior" ... and now it's working
fine ... THANKS

J.Pellechi

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Trouble getting the OFFSET fo work

Don,

I apologize, after spending wasted time on my own trying to get the code
to work, upon seeing your code I was just so happy to get mine to work
with a minor tweak ... after reading your response, however, I took the
time to study your approach and see you make no "Selection" ... so I
went back again and CHANGED my code to mirror yours. Definately short
and sweet ... Thanks alot for your time ...

J.Pellechi

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Trouble getting the OFFSET fo work