Change Row Height When Row is Selected

A

antifragile

New Member
Joined
Dec 22, 2014
Messages
13
Hi,

I would like a vba code that allows me to select the entire row of the selected cell, doubles the row height(or to certain height), and fills the row background color with, say, turquoise. Also, when I select another cell or row, I would like the previously selected row to go back to its original format, size and fill color.

So far I was able to figure out selecting the entire row of the selected cell, I can also increase the row height but I cannot have it go back to its original formatting when I select another cell or row.
My code is something like below:

Code: 
[COLOR=#011993][FONT=Menlo]Private[/FONT][/COLOR][COLOR=#011993][FONT=Menlo]Sub[/FONT][/COLOR][COLOR=#000000][FONT=Menlo] Worksheet_SelectionChange([/FONT][/COLOR][COLOR=#011993][FONT=Menlo]ByVal[/FONT][/COLOR][COLOR=#000000][FONT=Menlo] Target [/FONT][/COLOR][COLOR=#011993][FONT=Menlo]As[/FONT][/COLOR][COLOR=#000000][FONT=Menlo] Range)[/FONT][/COLOR][COLOR=#000000][FONT=Menlo]ActiveCell.EntireRow.Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]Selection.RowHeight = 32[/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo]EndSub[/FONT][/COLOR]

Any ideas are greatly appreciated.

Thanks,
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
here you go....


Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.RowHeight = 32
End Sub
 
Upvote 0
MUKESHY12390,

Thanks for your reply. I tried your suggestion but it does not revert the formatting back to original when I select a different cell.
Here is an example of what I want to achieve:

When I select D5, I want the entire row 5 to be selected and row height will be 32 (which I can with my code).
Also I would like the selected row to be filled with background color (yellow, blue, etc.)

WHEN I SELECT A DIFFERENT CELL (say B9)
I want the same formatting that was applied to row 5 when it was selected.
AND
I want row 5 to go back to its original formatting, (row height back to 16, no fill), as that row is no longer selected.

Hope this clarifies.

Thanks,
 
Upvote 0
Try this one

Code: 
Public strLastCell As String
Public strActiveCell As String
Public intLastCellHeight As Integer


Private Sub Worksheet_SelectionChange(ByVal Target As Range)






If strActiveCell = "" Then strActiveCell = ActiveCell.Address
strLastCell = strActiveCell
strActiveCell = ActiveCell.Address
intLastCellHeight = ActiveCell.RowHeight






Target.RowHeight = 32


Range(strLastCell).RowHeight = intLastCellHeight




End Sub
 
Last edited by a moderator:
Upvote 0
How about
Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Static Rng As Range
   If Not Rng Is Nothing Then
      Rng.RowHeight = 12.75
      Rng.EntireRow.Interior.Color = xlNone
   End If
   Target.RowHeight = 25.5
   Target.EntireRow.Interior.Color = 45678
   Set Rng = Target
End Sub
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
When a selection is changed from one row to another, my code changes row height of the previous row back to what it was and increase the new row height to 32. I had tested it few times before posting it. Thank you
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Setting multiple row heights in VBA
Replies
2
Views
183
Extivalis
E
V
  • Question
Stop Excel from Auto Resize Row Heights
Replies
5
Views
510
voodoo1226
V
PuntingJawa
  • Solved
Auto adjust row height as data changes
Replies
6
Views
623
Joe4
Joe4
I
  • Question
Paste to specific cell & keep row / column & width / height values
Replies
9
Views
386
HighAndWilder
H
I
  • Solved
Cell above is selected when i duplicate row
Replies
2
Views
87
ipbr21054
I

Forum statistics

Threads
1,215,020
Messages
6,122,712
Members
449,093
Latest member
Mnur

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top