user controls - How to highlight active row in excel and then return to base background color in VBA -

how highlight active row in excel in vba. , when row selected, return row base background color, , highlight new row.

also how clear rows highlighted, using clear button on user form.

so there tow question here, 1 high light , unhighlight active rows, , other clear high lights pressing clear button on form.

i know can highlight row using ret.entirerow.interior.colorindex = 6 cant find code unhighlight.

thanks help.

you can use 'clear all' functionality before changing color of row of cell navigated to.

open vb editor , right click --> view code on worksheet want row highlighting take place.

paste in code:

private sub worksheet_selectionchange(byval target range)      me.range("a1:xfd1048576").interior.colorindex = 0     target.entirerow.interior.colorindex = 6  end sub 

this code operates follows: whenever user changes or selected cell(s) on sheet, code first clear existing highlighting away in entire sheet , apply new highlighting row of target cell user has moved to.

this line of code:

worksheets("yoursheetname").range("a1:xfd1048576").interior.colorindex = 0

will clear colors cells in worksheet.

you may want limit range("a1:xfd1048576") usable range on workbook increase performance. on machine see subtle, still noticeable, delay in colors when move cells (because clearing cells in sheet instead of ones want). if this, wouldn't want use .entirerow attribute, instead have enumerate how far along workbook want row highlighted.

update

try code below, eliminates need clear entire worksheet. used .colorindex=xlnone instead of setting 0 should preserve table formatting. tested in excel 2010 , formatted data table, highlights correct row , unhighlights other row leaving table formatting in tact.

sub worksheet_selectionchange(byval target excel.range)     static rr      if rr <> ""         rows(rr).interior             .colorindex = xlnone         end     end if       r = selection.row     rr = r      rows(r).interior         .colorindex = 6         .pattern = xlsolid     end end sub 

the trick using static. allows variable continue exist after termination of procedure, remembers last row highlighted , performs un-highlight action accordingly.

the procedure first checks see rr set, if not moves on, if rr represents row highlighted.