PDA

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)