highlight the row after I input something in column a

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

Put this in the ThisWorkbook code module

[VBA]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[/VBA]

Quote:

---

Originally Posted by mikerickson

Put this in the ThisWorkbook code module

[vba]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[/vba]

---

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

Quote:

---

Originally Posted by clarksonneo

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.

Another approach would be to put conditional fomatting on all the cells with the formula
=(LEN($A1)>0)