VBA Macros for Conditional formatting and column hiding

[Allan54]

Hi All,

I am totally new to Macros. I need a Macro which should format a column based on the value of another column.

Consider I have 10 rows. I have to format column D, based on the value of Column E. If the value of Column E is > 1000, then the background color of Column D should be changed as green. The most important requirement is Column E should be invisible, Changing the font color of Column E as White does not seem ok cuz when we select the sheet entirely using Ctrl+A, the white values are very much visible. Can this be achieved using a macro?

Can anyone help please.

 

[a11jks]

What about just hiding the entire row? Click on row E, right click, hide. Also, you can protect the workbook and make it where none of the other cells are protected except for column e.

If you don't want that I have two more options I can think of.
1) Intersect macro that when you click on any cell in column e, it makes you select the cell in column d instead (i don't know how to just 'unselect' a cell)
2)Intersect macro that protects the workbook when you click on any E cell, then unprotects it when you click on any other cell.

 

[Allan54]

Hello allen
What you are asking can be done, but I cant do it now, xso if nobody has answered this for you
By the time I get home I will do it then
Cheers
Kevin

Hi Kevin,

Any solution for this issue?

 

[a11jks]

Will this work? Hide the entire column instead?

Private Sub Worksheet_Change(ByVal Target As Range)
    'loop through each cell in range
   For Each Row In Range("D:D")
       'exit loop if no more data to prevent infinite loop
       If Row.Value = "" Then
          Exit For
       End If
   
       'conditional formatting background color
       If Range(Row.Address).Offset(0, 1).Value > 1000 Then
          Range(Row.Address).Interior.Color = RGB(0, 153, 0) ' color cell green
       Else
          Range(Row.Address).Interior.ColorIndex = xlNone ' color cell with 'no fill'
       End If
   
      'set format to hide values in the cell in the next column
      'Range(Row.Address).Offset(0, 1).NumberFormat = ";;;"

   Next
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   'if column isn't hidden, hide it
   If Range("E:E").EntireColumn.Hidden = False Then
       Range("E:E").EntireColumn.Hidden = True
   End If

End Sub