automatically Highlight cells when typing

Welcome to the MrExcel board!

Assuming you don't have any cells already coloured that this will ruin (test in a copy of your workbook), this code should cover most circumstances. To implement ...

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.


<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    ActiveSheet.UsedRange.Interior.ColorIndex = xlNone<br>    ActiveCell.Interior.ColorIndex = 4<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Peter, thank you it worked.
Is there a way to highlight the row as well or instead?

Try this instead of the previous code.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> lCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.UsedRange<br>        .Interior.ColorIndex = xlNone<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>        lCol = .Find(What:="*", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlPart, _<br>            SearchOrder:=xlByColumns, Searchdirection:=xlPrevious, SearchFormat:=False).Column<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    lCol = IIf(ActiveCell.Column > lCol, ActiveCell.Column, lCol)<br>    ActiveCell.EntireRow.Resize(, lCol).Interior.ColorIndex = 4<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>