PDA

View Full Version : Macro for color fill



daedalus
09-18-2008, 02:16 PM
I am making a tracking spreadsheet and I want it to highlight certian columns for certian events to aid in project management while building a nuclear plant which has thousands of procedures. Basically making it easy to follow as the project goes on. I was going to use conditional formating but it was getting excessive and would be easier to do with a Macro. I have never writen a macro with VBA but once I have one that works I will be able to edit it and understand what to do somewhat.


Basically the format looks like the attachment(excel 2003)


I want it to show the row in green if before the start date, yellow after the start date, and red if not approved before the completion date. Once it is approved I would like the entire row up to the Approved Column to be green.

Then the ITAACs columns I want to make it so that if there are >0 the block shows up in red unless the ITAAC's completed column is equal to the ITAAC's involved column. If the columns are more than 0 and equal I would like them to be Green again.

Once the Completed box is checked I would like it to be green all the way accross the board(however it cannot be complete unless all ITAAC's are completed so I want the ITAAC's to remain seperate I don'twant them turning green just because someone puts an X in the complete column)



I hope you can follow that train of logic if not send me a PM and I will try to make sense of it. I am a Nuclear Mechanic so computers are more a hobby than anything so this is a little out of my normal element.

Thanks greatly I realize this is somewhat a complicated request even if you can just provide bits and pieces of the code I will try to piece it together as needed to make it work. I hope someone enjoys a good challenge.

RonMcK
09-18-2008, 02:52 PM
Thanks for adding the file. A suggestion for the future, use the Edit Message option** if you discover that you want to upload a file or change your original message. This will keep things a wee bit simpler on the forum.

** open your original message, you'll find Edit Msg in the lower right, beneath the msg frame and near the Quote button.

Oh! Sorry to hear about your network probs. - rjm


Thanks,

daedalus
09-22-2008, 08:55 AM
bump

Bob Phillips
09-22-2008, 09:24 AM
Not sure I am easy with the idea of contributing to the nuclera industry ... :-(



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

With Target

If .Row > 1 Then

If Not Intersect(Target, Me.Columns(3)) Is Nothing Then

.EntireRow.Interior.ColorIndex = xlColorIndexNone
If .Value > Date Then
.EntireRow.Interior.ColorIndex = 4
End If
ElseIf Not Intersect(Target, Me.Columns(4)) Is Nothing Or _
Not Intersect(Target, Me.Columns(14)) Is Nothing Then

.EntireRow.Interior.ColorIndex = xlColorIndexNone
If Me.Cells(.Row, "D").Value < Date Then
If Me.Cells(.Row, "N").Value <> "Y" Then
.EntireRow.Interior.ColorIndex = 3
Else
Me.Cells(.Row, "A").Resize(, 14).Interior.ColorIndex = 4
End If
End If
ElseIf Not Intersect(Target, Me.Columns(15)) Is Nothing Or _
Not Intersect(Target, Me.Columns(16)) Is Nothing Or _
Not Intersect(Target, Me.Columns(17)) Is Nothing Then

If Me.Cells(.Row, "Q").Value = "Y" Then
.EntireRow.Interior.ColorIndex = 4
If Me.Cells(.Row, "O").Value <> "Y" Then
Me.Cells(.Row, "O").Interior.ColorIndex = xlColorIndexNone
End If
If Me.Cells(.Row, "P").Value <> "Y" Then
Me.Cells(.Row, "P").Interior.ColorIndex = xlColorIndexNone
End If
End If
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select'the View Code option from the menu, and paste the code in.

daedalus
09-22-2008, 10:19 AM
Thanks for the help

I understand the general fear of nuclear power but its actually much safer than you realize, and of course cleaner. It could also solve the major power issues in Chile caused by the heavy industries like Copper mining and stuff.

daedalus
09-23-2008, 10:56 AM
I still can't get this to work I have tried manipulating it a dozen different ways and so far I haven't had a single thing change color

Kenneth Hobs
09-23-2008, 11:17 AM
Maybe if you posted a manually marked example with say 5 or 6 rows, it would be a tad easier to test.

You will note in xld's code that he used "Y" to determine if the row was approved. You may be using "X" or "x".


When you markup the example, you can make notes somewhere in it explaining why it is marked that way. Be sure to include all 3 cases when you make a condition. e.g. 1=before a date, 2=on a date or 3=after a date. Usually, one just combines this into two conditions of Today<=some date or Today>some date.

Bob Phillips
09-23-2008, 11:19 AM
I had it changing colours as I changed cell values.

Did you put thecode in the right place as directed.