please see attached file in the attached file you can see seventh row highlighted in yellow color like that highlight current row in yellow color if i move to 8 number
row then highlight that row but color should be disappear of previous row and should be only current row highlight in yellow color if i move to tenth row it should highlighted in yellow color please give me VBA code for that process thanks in advance
Try ....
Click on column D
Please Login or Register to view this content.
Last edited by JohnTopley; 09-24-2024 at 04:34 AM.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
You need to store the old color into a variable.
Copy this code in sheet1 module
PHP Code:Option Explicit
Dim r As Long ' Variable to store the previously selected row
Dim originalColors() As Variant ' Array to store the original background color of each cell in the row
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim currentR As Range
Dim i As Long ' Index for looping through the columns
Dim numCols As Long ' Number of columns in the row
' Check if the selected cell is within the range A5:AI100
If Intersect(Target, Range("A5:AI100")) Is Nothing Then Exit Sub
' Determine the number of columns in the row (from column A to column AI)
numCols = Range("A5:AI5").Columns.Count
' If r > 0 (meaning a row was previously selected), restore the color of the previous row
If r > 0 Then
Set currentR = Range(Cells(r, 1), Cells(r, "AI"))
For i = 1 To numCols
' Restore the original color of each cell in the previous row
currentR.Cells(1, i).Interior.Color = originalColors(1, i)
Next i
End If
' Update r to the currently selected row
r = Target.Row
' Save the original background color of each cell in the newly selected row
ReDim originalColors(1 To 1, 1 To numCols) ' Initialize the array to store colors
For i = 1 To numCols
originalColors(1, i) = Cells(Target.Row, i).Interior.Color
Next i
' Highlight the entire newly selected row in yellow
Range(Cells(Target.Row, 1), Cells(Target.Row, "AI")).Interior.Color = vbYellow
End Sub
Quang PT
I do not need to store old color just want to color current row if i am in row 1 only show that row in yellow color if i move to row 2 then only show row 2 in yellow color
but at that time do not show row 1 in yellow color also i want that if i am moving from any cell for example i am in cell A2 Or B2 Or C2 Or D2 or any cell in row to this code should be work if i am in cell e5 or g5 then only highlight row 5 please do this changes in this VBA code
Did you try post #2 code? Could easily be changed to any cellse;lection in a row (rather than just column D).
Updated codePlease Login or Register to view this content.
Maybe this would suffice?
Please Login or Register to view this content.
Thanks Mr. Bytemarks but i am confuse whom i give reputation point you four person help me but now i giving you reputation point
Last edited by keshavtale; 09-24-2024 at 05:36 AM.
@John...
You can replace...
With...Please Login or Register to view this content.
Also best to make use of...Please Login or Register to view this content.
Please Login or Register to view this content.
Good Luck...
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
Also....Add a comment if you like!!!!
And remember...Mark Thread as Solved...
Excel Forum Rocks!!!
@Sintek: having investigated "CountLarge" I view it in the same way as Excel having 1M rows/16K columns: of no value to anyone as volumes of that size would cripple Excel!
Pure mathematics vs Applied Mathematics!
@ John...True...guessing nobody else...like me...Will select all [triangle] in a sheet lol...
Thank you. Pleased to have contributed.Originally Posted by keshavtale
I use it because it's an easy way to avoid the possibility of an overflow error.
Last edited by ByteMarks; 09-24-2024 at 06:30 AM.
@bytemarks: agree but an overflow error of that magnitude is unlikely (IMHO). Equally, I except it is best (good) practice and does no harm!
It would be interesting to know from the forum professioinals what the maximum size spreadsheet that they have encountered : in partucular number of columns as we do see 500K+ rows occassionally but I have yet to see even 1000 columns.
Cannot imagine the need for this...not even with a database...but then again...with the amount of unusual postings on this forum...anything is possible lol...but I have yet to see even 1000 columns.
It would be interesting to know. Never even come close to 500 columns. I think the max I've encountered legitimately is around 150. The most rows was 800k some years ago in Excel 2007 which was...er...interesting.
@sintek/@bytemarks: Thank you for your responses
.... so I wonder what were MS thinking when they increased the row/column sizes to the current values ?
...Interesting...
I think Bill Gates just looked at his current bank balance before the decision meeting and chose a number of cells to match.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks