conditional formatting and conditional date programming?

D

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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
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
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
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
You must log in or register to reply here.

Similar threads

A
  • Solved
Ramp up formula with variable column start
Replies
4
Views
105
akatsuki
A
M
  • Solved
FILTER Function & MAP/LAMBDA through Different Ranges & Arrays
Replies
1
Views
106
Fluff
Fluff
T
  • Solved
Conditional Format - Column Date Range & Another Variable
Replies
3
Views
61
Joe4
Joe4
S
  • Locked
  • Question
Look up the values according to the conditions
Replies
1
Views
114
Peter_SSs
Peter_SSs
S
  • Question
Returning values with multiple conditions
Replies
0
Views
135
sslll
S

Forum statistics

Threads
1,214,427
Messages
6,119,419
Members
448,895
Latest member
omarahmed1

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