View Full Version : highlight the row after I input something in column a
clarksonneo
08-04-2011, 11:09 AM
Hi,
I want a macro that can perform the following task.
Could you please write me the macro?
Task:
For each worksheet in the workbook,
and for each cell in column A,
after I input something in a cell in column a, the row will be filled by red colour.
However, if the things in a cell in column a is deleted, the red colour will be removed from that row.
thanks
mikerickson
08-04-2011, 07:02 PM
Put this in the ThisWorkbook code module
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
With Application.Intersect(Target, Sh.Columns(1))
.SpecialCells(xlCellTypeBlanks).EntireRow.Interior.ColorIndex = xlNone
.SpecialCells(xlCellTypeConstants).EntireRow.Interior.ColorIndex = 3
End With
On Error GoTo 0
End Sub
clarksonneo
08-04-2011, 09:01 PM
Put this in the ThisWorkbook code module
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
With Application.Intersect(Target, Sh.Columns(1))
.SpecialCells(xlCellTypeBlanks).EntireRow.Interior.ColorIndex = xlNone
.SpecialCells(xlCellTypeConstants).EntireRow.Interior.ColorIndex = 3
End With
On Error GoTo 0
End Sub
thank you for your reply.
but there is a problem.
For example:
If I input something in cell C5, row 5 will not be filled by red colour.
However, after I input something in cell A6, then both row 5 and 6 will be filled by red colour.
If I delete the thing in a6, the red colour on row 6 will be removed.
However, the red colour in row 5 will not be removed.
thanks
thanks
Aussiebear
08-07-2011, 03:27 AM
I want a macro that can perform the following task.
Task:
For each worksheet in the workbook,
and for each cell in column A,
after I input something in a cell in column a, the row will be filled by red colour.
However, if the things in a cell in column a is deleted, the red colour will be removed from that row.
This what you asked for,and that is what Mike kindly gave you.
mikerickson
08-07-2011, 11:37 AM
Another approach would be to put conditional fomatting on all the cells with the formula
=(LEN($A1)>0)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.