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.

 

[kevin lazell]

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

 

[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

Sure. Do post the answer.. Thank u so much

 

[Allan54]

Can it be done without hiding the Column E? My exact requirement is the value in column E should be invisible both in sheet and in the formula bar but still it has to be used for the conditional formatting of Column D. Is this all possible, Please let me know.

Thanks in Advance

 

[kevin lazell]

hi allen
why are you using ctrlA are you maybe copying the whole sheet

 

[Allan54]

hi allen
why are you using ctrlA are you maybe copying the whole sheet

yeah, when we try to copy it as a whole sheet, its visible

 

[a11jks]

Try this.

My formula is cell D is just for testing purposes. I wanted random data to test

Sheet1

	A	B	C	D
1			Test1	=ROUND(RAND(),0)*1001
2			test2	=ROUND(RAND(),0)*1001
3			test3	=ROUND(RAND(),0)*1001
4			test4	=ROUND(RAND(),0)*1001
5			test5	=ROUND(RAND(),0)*1001
6			test6	=ROUND(RAND(),0)*1001
7			test7	=ROUND(RAND(),0)*1001
8			test8	=ROUND(RAND(),0)*1001
9			test9	=ROUND(RAND(),0)*1001
10			test10	=ROUND(RAND(),0)*1001

<tbody>
</tbody>

Sheet 1 (Sheet1)

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

 

[Allan54]

Try this.

My formula is cell D is just for testing purposes. I wanted random data to test

Sheet1

	A	B	C	D
1			Test1	=ROUND(RAND(),0)*1001
2			test2	=ROUND(RAND(),0)*1001
3			test3	=ROUND(RAND(),0)*1001
4			test4	=ROUND(RAND(),0)*1001
5			test5	=ROUND(RAND(),0)*1001
6			test6	=ROUND(RAND(),0)*1001
7			test7	=ROUND(RAND(),0)*1001
8			test8	=ROUND(RAND(),0)*1001
9			test9	=ROUND(RAND(),0)*1001
10			test10	=ROUND(RAND(),0)*1001

<tbody>
</tbody>

Sheet 1 (Sheet1)

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

The value in column E is hidden, but my requirement is I want the data of column E to be hidden from displaying the data in formula bar also. Is that possible by anyway?

 

[a11jks]

You can always use sheet protection. Select all of column D, right click on a cell in column D, format cells, protection. From there make to check both locked and hidden.
After that go to the review tab (xl 2007+ i believe) and protect both the workbook and worksheet.

 

[Allan54]

You can always use sheet protection. Select all of column D, right click on a cell in column D, format cells, protection. From there make to check both locked and hidden.
After that go to the review tab (xl 2007+ i believe) and protect both the workbook and worksheet.

Thanks for the quick reply, Any option to achieve it without protecting the sheet?