conditional formatting and conditional date programming?

dbansal1

New Member
Joined
Aug 15, 2007
Messages
5
<html>

<body>



Hey guys,



I hope you can help me out with this



I have a spreadsheet that looks like the following:</p>
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%">
<tr>
<td width="25%">A</td>
<td width="25%">B</td>
<td width="25%">C</td>
<td width="25%">D</td>
</tr>
<tr>
<td width="25%">Project</td>
<td width="25%">Status</td>
<td width="25%">Start Date</td>
<td width="25%">End Date</td>
</tr>
<tr>
<td width="25%">A</td>
<td width="25%">Closed</td>
<td width="25%">4/1/2007</td>
<td width="25%">5.1/2007</td>
</tr>
<tr>
<td width="25%">B</td>
<td width="25%">Open</td>
<td width="25%">5/1/2007</td>
<td width="25%">6/1/2007</td>
</tr>
<tr>
<td width="25%">C</td>
<td width="25%">In Progress</td>
<td width="25%">7/1/2007</td>
<td width="25%">12/1/2007</td>
</tr>
</table>


So now, the first question is a conditional formating question, (i looked
this up on google, but i found this mod (row??? command, could not get it to
work though) so column B is composed of drop down boxes with those 3 options.
Now, i would like it so when the closed option is chosen the entire row turns
grey aka, a certain format.



Secondly, It seems that we have a lot of projects that are similar to projects A
and B above. The duration period has ended yet they still show as open. How do I
program excel so it automatically changes the status of these projects to closed
and subsequently turns that row gray or whatever format?



Thank you for looking and helping me out!</p>

</body>

</html>
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Dylan

Board Regular
Joined
Jul 27, 2007
Messages
150
For 1st portion.
Use the Condition Formating.
Select whole range, goto Format>Condition Formating, select "Formula is", enter =$B2="Closed" then select the formating that you want.

2nd portion.
Try this code.

Code:
Sub test()
lastRow = Cells(Rows.Count, 4).End(xlUp).Row
For I = 2 To lastRow
    MyDate = Date
    If Cells(I, 4).Value < MyDate Then
        Cells(I, 2).Value = "Closed"
    End If
Next I

End Sub
 
Upvote 0

dbansal1

New Member
Joined
Aug 15, 2007
Messages
5
I select the whole range, and if the cell is selected as closed then yes the row does highlight grey. However, when i change that cell back to "open" only that cell changes color, not the entire row (I applied formatting for open also, doesnt work)
 
Upvote 0

Dylan

Board Regular
Joined
Jul 27, 2007
Messages
150
It work for me. Please makesure you enter the formula with $ sign to lock the column B.
For "Open" formatting, please double confirm you enter (in formating) exactly as in your list.
Would you mind to send the file to me, so that I can have a look on it?[/img]
 
Upvote 0

Forum statistics

Threads
1,191,034
Messages
5,984,261
Members
439,881
Latest member
Amitoj95

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
Top