Automatically Color Rows and Columns Based on Mouse Pointer
Automatically Color Rows and Columns Based on Mouse Pointer. With a large spreadsheet containing many rows and columns, it can be difficult to keep track of which row and column you are currently selecting. Today, Buffcom will guide you on how to automatically color cells based on the mouse pointer, which helps prevent data entry or verification errors.
Automatically Color – Coloring Rows
First, select the range of data and click on Home, then select Conditional Formatting -> New Rule.
In the new window, select “Use a formula to determine which cells to format”, then enter the formula =Row()=Cell(“Row”) in the “Format values where this formula is true” box. Select the desired fill color, then click OK.
Now, the color of the row has been applied, but it does not automatically follow the mouse pointer when you click on other rows. You can press F9 to manually refresh, but to automatically follow the mouse pointer, continue with the following steps.
Right-click on the sheet, select “View code”, and then copy and paste the following code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = True
With this code, the row where the mouse pointer is located will automatically change color.
To color columns instead of rows, follow the same steps as above but replace the formula with =Column()=Cell(“col”).
Finally, if you want to color individual cells based on the mouse pointer, use the formula =and(Row()=Cell(“Row”),Column()=Cell(“col”)) in the “Format values where this formula is true” box.
In this way, you can easily manage and keep track of the selected cell, row, and column by the visual color changes.