Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble getting the OFFSET fo work
glad to help. I hope you tried mine
-- Don Guillett SalesAid Software "microsoft.public.excel.programming" wrote in message ... 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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble getting the OFFSET fo work
Glad to steer you in the right direction. Selections should be avoided if
unnecessary. -- Don Guillett SalesAid Software "microsoft.public.excel.programming" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Having trouble getting MATCH to work with a variable lookup array | Excel Worksheet Functions | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Worksheet Functions | |||
Trouble getting new toolbar buttons to work properly in Excel 2003 | Excel Worksheet Functions | |||
Offset Function Trouble, Please Help | Excel Worksheet Functions | |||
More work schedule creation trouble | Excel Discussion (Misc queries) |