Highlighting Row When A Cell In That Row Is Selected

[KAELondon]

Hello! I have a very specific issue that I need help solving. I was able to accomplish the goal of highlighting an entire row in a table of information when a cell (in that row) is highlighted with the help of this little bit of conditional formatting and VBA (which I found on thesmallman but can't post a link because I'm too new):

THE FORMATTING PART:
=OR(CELL("row")=CELL("row",A1))

THE VBA PART:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Calculate
End Sub

It does the job well, however, I have cells and data to the right of my table and don't want rows highlighted when working outside the table. The above trick works at highlighting just the cells in the table (that's the conditional formatting part I guess so it makes sense) but it highlights that row even if I'm working outside of the cells formatted (which I guess means that I should define the range of columns in which the VBA should kick in). Can someone please help me with this?

Thank you very much in advance.

K

[Keebellah]

Hi, welcome to the forum.
Yo should try your best friend Google

Try these links
https://www.extendoffice.com/documen...nd-column.html
https://trumpexcel.com/highlight-act...w-column-excel

and there are more

[KAELondon]

Hi Keebellah, thank you for your reply. I checked those out but they don't work better than the one I was using because they use VBA to highlight which kills any formatting that was in those cells before.

The VBA to Conditional Formatting route is definitely better I just need a way to define when the VBA should work and apply the Conditional Formatting. Can't seem to find it on Google though.

Thanks again though,

K

[Keebellah]

Oh, I thought that one of those had a section that stored the set formats and undid once you moved from cell to cell, I also saw one somewhere using conditional formatting rules, will see if I can find it it else put it together for you

[Keebellah]

Since I do not have a sample file and you mention a table, in this case I assume you're speaking about a ListObject or just a Table?
With a ListObject you can restrict actions to the area within the table, I understand what you mean about the vba and the formatting, but there is some code around there that saves the current situation and restores it when you select another cell, something with undo and so.

[karmapala]

Hi KAELondon,

I'm sorry if I'm not helping here.

I just wonder what does it mean on below quote:

I was able to accomplish the goal of highlighting an entire row
in a table of information when a cell (in that row) is highlighted
with the help of this little bit of conditional formatting and VBA

To me it seems like this :
First accomplishment :
highlighting a cell in row n of column X via "manual" conditional formatting, say for example cell B10

Second accomplishment :
(based on cell B10 which already highlighted) highlighting row 10 within the table range column via VBA.

Please tell me if I'm correct or wrong.

Please Login or Register  to view this content.

Since to be honest I don't quite understand what does the formula above means,
would you please tell me on what kind of condition you want the cell is highlighted (via "manual" conditional formatting) ?

Thank you.

[Keebellah]

I found this link and it works and also keeps your own formatting and conditional formatting active.
I've attached the link and a file with fake data to show you (Fake Data can be generate at will of the Fake Datagenerator site)

https://trumpexcel.com/highlight-act...-column-excel/

[KAELondon]

Yeah, the trick I found applies the "highlighting" of the row via conditional formatting and then (I guess) that's activated through the line of VBA code. I actually have no idea how that part works. So the highlighted cells are in the range I desire (because it's part of the formatting for those cells and the other cells in that row don't have it I suppose) but the highlighting is still triggered even when I'm working outside of those cells. I wish I could upload the spreadsheet so you could see but being a noob it doesn't allow me yet. I'll check out the stuff you've posted. Hopefully it'll do the trick.

[KAELondon]

Hi KAELondon,

I'm sorry if I'm not helping here.

I just wonder what does it mean on below quote:


To me it seems like this :
First accomplishment :
highlighting a cell in row n of column X via "manual" conditional formatting, say for example cell B10

Second accomplishment :
(based on cell B10 which already highlighted) highlighting row 10 within the table range column via VBA.

Please tell me if I'm correct or wrong.

Please Login or Register  to view this content.

Since to be honest I don't quite understand what does the formula above means,
would you please tell me on what kind of condition you want the cell is highlighted (via "manual" conditional formatting) ?

Thank you.

That formula applies the conditional formatting. The "A1" bit is arbitrary; it's the top left cell from where the formatting applies. I think on my sheet it's actually "C5" but yeah. That all works fine. It's my lack of knowledge with VBA that can't define the range of cells that trigger the formatting.

[KAELondon]

[QUOTE=Keebellah;5345892]I found this link and it works and also keeps your own formatting and conditional formatting active.
I've attached the link and a file with fake data to show you (Fake Data can be generate at will of the Fake Datagenerator site)



So I just gave this a whirl and it's doing the same problem. It highlights the rows all fine (though it does columns too which I don't really need) but it still highlights the row even if you go out of the range of the data, say to column "Z" for example. Even where it doesn't highlight the column anymore it still highlights that row.

Damn, can't even post a link yet! Being a noob sucks!

[karmapala]

Maybe try to put a line something like this ?

Please Login or Register  to view this content.

the value of the column depends on what is the last column of your data. (column 19 is column S).

before

Please Login or Register  to view this content.

Something like this ?
2020-06-09_06-31-08.gif

[Keebellah]

Have you even take the time to look at my file and the way that one works? The explanation is all there as it is on the site I linked there

[AC PORTA VIA]

try using below for conditional formatting
=ADDRESS(ROW(),COLUMN())=CELL("address")

[Keebellah]

All you need to do is remove the column part I. Conditional format rule 😱
An yes after z the column is empty so no column color , duh!!!

[KAELondon]

Maybe try to put a line something like this ?

Please Login or Register  to view this content.

the value of the column depends on what is the last column of your data. (column 19 is column S).

before

Please Login or Register  to view this content.

Something like this ?
Attachment 681600

And we have a winner! So simple and works like a charm! I thank you sir!

[KAELondon]

And we have a winner! So simple and works like a charm! I thank you sir!

Well... almost like a charm. For some reason on my sheet it leaves the last highlighted row still highlighted. Strange? Any ideas on this?

[Keebellah]

Probably incorrect defined row? Check the link I posted has two samples one for row only one for column only and of course both combined

[Keebellah]

Check the file I attached, modified the CF for row only

[karmapala]

Well... almost like a charm.
For some reason on my sheet it leaves the last highlighted row still highlighted.
Strange? Any ideas on this?

Sorry my bad.
It's not strange, as in my side it also still leave the last highlighted row,
while the image in my post, I make another code to get rid off that last highlighted row .

Here is my way to get rid off that last highlighted row :
Please make a new module, so there is no "Option Explicit",
copy the code below then paste to the new module.

Please Login or Register  to view this content.

And for the worksheet module (again, please no "Option Explicit") :

Please Login or Register  to view this content.

The code above set the last column header number (cl) and the the last row number in column A (lr)
If the active cell is bigger than cl or bigger than lr,
it will remove the Conditional Formatting in the sheet (by calling the stopFill Sub),
this stopFill Sub will not remove the "fill" formatting in the sheet.
Then it directly exit the sub.

If the active cell is smaller than cl or smaller than lr (so it's within the "table range"),
then it put the Conditional Formatting in the sheet (by calling the setFill Sub).
setFill Sub won't do Conditional Formatting each time it's called,
as it will check first whether the sheet has any Conditional Formatting or not (yes/no).
If yes, then it directly exit the sub. If no, then it write the Conditional Formatting.

PS:
Please don't forget to change the code where it read Range("A:S") according to your need,
and also change the ("Sheet1") according to your need.

[Keebellah]

Option Explicit is something you should always invoke, this forces you to dimension the variables.
It only occurs at the top of the VBA ONCE
The way this is going, I think I'll unsubscribe to this post.
The code in the fakedata file works, and does NOT leave a highlighted row, so you're doing something wrong.
Or you attach a copy of your file with non sensitive data or we leave it like this and you'll figure it out someway

[karmapala]

Option Explicit is something you should always invoke, this forces you to dimension the variables.
It only occurs at the top of the VBA ONCE

I understand that, Keebellah.

But to be honest, to me Option Explicit made me confuse.
This maybe because I'm not used to have it and I never do a very long complicated code with so many variables .
As long as the code works as expected, that's why I don't do Option Explicit and declaring variable to the sub.

The code in the fakedata file works,
and does NOT leave a highlighted row

I've downloaded the fake data in post #18.
Yes it works, but I don't know why in my side when I click any row within column larger than column S (say I click cell V15),
although no row in columns larger than column S is highlighted but cell A15 to S15 still highlighted.
So I thought that maybe this is what the OP doesn't want it.

Anyway, I'm thankful to you for providing the sample workbook, Keebellah.
From your sample workbook I learn new things,
because at first I don't understand at all about the formula in Conditional Formatting in post #1,
and I don't understand what is Target.Calculate.

[Keebellah]

Well, now's the time to learn.
Option Explicit how large the code is is something that comes in useful to avoid using incorrect values with the variables.
A number is a number a string is a string, etc.
Target.Calculate is just that the Target is the currently active cell.

I think you forgot to copy the Selection change part in the worksheet where you are working with.
Checkh that in the sample file, just copy the code in the worksheet VBA

[karmapala]

Well, now's the time to learn.

Yes, I know now what =OR(CELL("row")=CELL("row",A1)) means.

Option Explicit how large the code is is something that comes in useful
to avoid using incorrect values with the variables.
A number is a number a string is a string, etc.
Target.Calculate is just that the Target is the currently active cell.

Thank you for the explanation, Keebellah.

I think you forgot to copy the Selection change part in the worksheet where you are working with.
Checkh that in the sample file, just copy the code in the worksheet VBA

Just now I checked the sample file from post #18.

What I found in the HiLiteModule :

Please Login or Register  to view this content.

And what I found in the Sheet1 module :

Please Login or Register  to view this content.

But I don't do copy paste to my own worksheet, Keebellah.
I just directly have a look on the file (FakeNameGenerator.com_52ba767e.xlsm‎).
The data range of that file is from column A1 to S1201.
But I don't know why in my side when I click (for example) cell V15, cell A15 to S15 is highlighted.

[Keebellah]

You do NOT need OR if you're only checking the row.

[karmapala]

You do NOT need OR if you're only checking the row.

I don't do anything to the file, Keebellah.

The green comment is already there in HiLiteModule.

Please Login or Register  to view this content.

While the formula in the CF :

Please Login or Register  to view this content.

is also already there, and I also don't do anything to the formula in the CF.

1. I downloaded the FakeNameGenerator.com_52ba767e.xlsm file in post #18.
2. After it downloaded to my computer, I open the file.
3. I directly click cell V15 ---> Result : cell A15 to S15 is highlighted.
4. I click cell Z30 ---> Result : cell A30 to S30 is highlighted.
and so on.

I don't know why in my side, when clicking cell V15, still causing A15 to S15 highlighted,
while in your side it seems cell A15 to S15 is NOT highlighted when click cell V15 ?

Please have a look on the image below :
2020-06-10_17-44-18.gif

Once again, the result like the animation above is just after I open the file,
reduce the column width then capture the animation.
I don't even have a look to the sub and to the formula in CF.


Then I post the code by adding this line in Sheet1 module:

Please Login or Register  to view this content.

But the result is still not the OP expected result,
because although the highlighted is not moving anymore - but the last highlighted row stay there as seen in the image below :
2020-06-10_17-52-30.gif

So, the OP say :

Well... almost like a charm. For some reason on my sheet it leaves the last highlighted row still highlighted

I understand it because logically the OP hope the result as seen in the image on my post #11.

After I know for sure what the OP expected result is, I put the code on post #19 to get the result like the image on post #11.

That's the story, Keebelah.
I'm very sorry for the confusion.

[Keebellah]

In the CF there is a region (area or range if you want) where it applies to.
If the selected cell falls outside this range then nothing happens so the last highlight remains.
So simple, increase the CF area if applies to.

[KAELondon]

Sorry my bad.
It's not strange, as in my side it also still leave the last highlighted row,
while the image in my post, I make another code to get rid off that last highlighted row .

Here is my way to get rid off that last highlighted row :
Please make a new module, so there is no "Option Explicit",
copy the code below then paste to the new module.

Please Login or Register  to view this content.

And for the worksheet module (again, please no "Option Explicit") :

Please Login or Register  to view this content.

The code above set the last column header number (cl) and the the last row number in column A (lr)
If the active cell is bigger than cl or bigger than lr,
it will remove the Conditional Formatting in the sheet (by calling the stopFill Sub),
this stopFill Sub will not remove the "fill" formatting in the sheet.
Then it directly exit the sub.

If the active cell is smaller than cl or smaller than lr (so it's within the "table range"),
then it put the Conditional Formatting in the sheet (by calling the setFill Sub).
setFill Sub won't do Conditional Formatting each time it's called,
as it will check first whether the sheet has any Conditional Formatting or not (yes/no).
If yes, then it directly exit the sub. If no, then it write the Conditional Formatting.

PS:
Please don't forget to change the code where it read Range("A:S") according to your need,
and also change the ("Sheet1") according to your need.

So I've given this a try and it's not working. It could be my novice skills at work but I did just copy/paste that so not sure what I'm doing wrong. There are other subs there doing a few different functions so maybe it's something to do with that (though I don't think so because they're working fine). Perhaps I can email you the spreadsheet so you can see it first hand?

Thank you for your efforts guys. I'm learning even if not getting to the ultimate goal yet. I really appreciate it.

Regards,

K

[Keebellah]

I'm unsubscribing from this post.
The discussions are fine but keep running around in circles.
You have code to do something and then code to correct incorrect application of the first code.
Well. all the best and happy coding

[karmapala]

So I've given this a try and it's not working.

Hi KAE,
Please have a look at the attachment.

[karmapala]

Thank you Keebellah for your help.

[Keebellah]

Well here is your file without all the macros and it works