I have to update a sheet every week. Any idea, how to have a column be
highlighted and every cell be un-colored when updated with the new values
inserted, even if the new values of a cell remain to be the same.
--
Best Regards,

Faraz

Hi Faraz

I assume the sheet is having a header row (Row 1) with the week
number/date..The below Activate event will check for filled cells. If no
cells are filled except Row1 the column is highlighted.

Once you fill data the cell highligtion is taken off. Not sure whether this
is what you are looking for.

Private Sub Worksheet_Activate()
Dim lngCol As Long, lngLastCol As Long
lngLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
For lngCol = 2 To lngLastCol
Columns(lngCol).Interior.ColorIndex = xlColorIndexNone

'If highligtion is based on number of cells filled in that column
If WorksheetFunction.CountA(Columns(lngCol)) <= 1 _
Then Columns(lngCol).Interior.ColorIndex = 15

'OR

'if the 1st row is having dates Highligtion based on dates
If Cells(1,lngCol)Date then Columns(lngCol).Interior.ColorIndex = 15

Next
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then Target.Interior.ColorIndex = xlColorIndexNone
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

I have to update a sheet every week. Any idea, how to have a column be
highlighted and every cell be un-colored when updated with the new values
inserted, even if the new values of a cell remain to be the same.
--
Best Regards,

Faraz

XClent idea!

Never had the idea of using Interior.ColorIndex concept!!!

Thanx pal!
--
Best Regards,

Faraz


"Jacob Skaria" wrote:

Hi Faraz

I assume the sheet is having a header row (Row 1) with the week
number/date..The below Activate event will check for filled cells. If no
cells are filled except Row1 the column is highlighted.

Once you fill data the cell highligtion is taken off. Not sure whether this
is what you are looking for.

Private Sub Worksheet_Activate()
Dim lngCol As Long, lngLastCol As Long
lngLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
For lngCol = 2 To lngLastCol
Columns(lngCol).Interior.ColorIndex = xlColorIndexNone

'If highligtion is based on number of cells filled in that column
If WorksheetFunction.CountA(Columns(lngCol)) <= 1 _
Then Columns(lngCol).Interior.ColorIndex = 15

'OR

'if the 1st row is having dates Highligtion based on dates
If Cells(1,lngCol)Date then Columns(lngCol).Interior.ColorIndex = 15

Next
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then Target.Interior.ColorIndex = xlColorIndexNone
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

I have to update a sheet every week. Any idea, how to have a column be
highlighted and every cell be un-colored when updated with the new values
inserted, even if the new values of a cell remain to be the same.
--
Best Regards,

Faraz