VBA Macros for Conditional formatting and column hiding

A

Allan54

New Member
Joined
Oct 11, 2013
Messages
11
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.
 
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
Try this.

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

Sheet1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Test1
[/TD]
[TD]=ROUND(RAND(),0)*1001
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]test2
[/TD]
[TD]=ROUND(RAND(),0)*1001
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD]test3
[/TD]
[TD]=ROUND(RAND(),0)*1001
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD]test4
[/TD]
[TD]=ROUND(RAND(),0)*1001
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD]test5
[/TD]
[TD]=ROUND(RAND(),0)*1001
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD]test6
[/TD]
[TD]=ROUND(RAND(),0)*1001
[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD]test7
[/TD]
[TD]=ROUND(RAND(),0)*1001
[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD]test8
[/TD]
[TD]=ROUND(RAND(),0)*1001
[/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD][/TD]
[TD][/TD]
[TD]test9
[/TD]
[TD]=ROUND(RAND(),0)*1001
[/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD]test10
[/TD]
[TD]=ROUND(RAND(),0)*1001
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 1 (Sheet1)
Code: 
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
 
Upvote 0
a11jks said:
Try this.

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

Sheet1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Test1 [/TD]
[TD]=ROUND(RAND(),0)*1001[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]test2 [/TD]
[TD]=ROUND(RAND(),0)*1001[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD]test3[/TD]
[TD]=ROUND(RAND(),0)*1001[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD]test4[/TD]
[TD]=ROUND(RAND(),0)*1001[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD]test5[/TD]
[TD]=ROUND(RAND(),0)*1001
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD]test6[/TD]
[TD]=ROUND(RAND(),0)*1001[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD]test7[/TD]
[TD]=ROUND(RAND(),0)*1001[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD]test8[/TD]
[TD]=ROUND(RAND(),0)*1001[/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD][/TD]
[TD][/TD]
[TD]test9[/TD]
[TD]=ROUND(RAND(),0)*1001[/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD]test10[/TD]
[TD]=ROUND(RAND(),0)*1001[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 1 (Sheet1)
Code: 
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
Click to expand...


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?
 
Upvote 0
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.
 
Upvote 0
a11jks said:
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.
Click to expand...

Thanks for the quick reply, Any option to achieve it without protecting the sheet?
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
vba run time error 424
Replies
3
Views
105
Peter_SSs
Peter_SSs
N
  • Solved
Help with creating a macro
Replies
2
Views
184
Peter_SSs
Peter_SSs
D
  • Question Question
Conditional Formatting Help Needed!
2
Replies
15
Views
384
coulombevin
coulombevin
A
  • Solved
Need help with worksheet/conditional formatting/macros please
Replies
7
Views
92
Aphten
A
E
  • Question Question
Specific VBA Function
Replies
0
Views
303
EAlexWolfe
E

Forum statistics

Threads
1,226,837
Messages
6,193,256
Members
453,784
Latest member
Chandni

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top