Conditional Formating based on another cell
 

I have 2 columns of concern DATE and COST.
I would like the COST cell to be shaded if the DATE cell is less than a
specificed date.
Then if it is possible to calculate the COST field but only the ones
highlighted.

Help is greatly appreciated!!

Thanks, Stacey

For the conditional format, use the following for the condition of the cost
column:

=$A$2<TODAY()
and so on, where cell A2 is the corresponding date column. Select the
appropriate shade on the pattern tab.

As far as calculating the cost column if it is highlighted, my only thoughts
are to write a macro that you can run that checks for all cost cells in the
column and performs the functions if the cell is shaded. Use an outside loop
that loops for all cells in the cost column with the following code inside:

If Range("B2").Interior.ColorIndex = 6 Then 'or whatever your shade color is
Range("B2").Formula = "hello" 'type whatever your formula is here
End If

Hope this helps!

"SMac" wrote:

I have 2 columns of concern DATE and COST.
I would like the COST cell to be shaded if the DATE cell is less than a
specificed date.
Then if it is possible to calculate the COST field but only the ones
highlighted.

Help is greatly appreciated!!

Thanks, Stacey

I copied your formula and it worked but I want to enter a date i.e. 3/1/05
but it doesn't seem to work, I have: =$A$1<"3/5/05"
Where A1 is 3/8/05.

I will look into the macro.

Thanks!

"Chad" wrote:

For the conditional format, use the following for the condition of the cost
column:

=$A$2<TODAY()
and so on, where cell A2 is the corresponding date column. Select the
appropriate shade on the pattern tab.

As far as calculating the cost column if it is highlighted, my only thoughts
are to write a macro that you can run that checks for all cost cells in the
column and performs the functions if the cell is shaded. Use an outside loop
that loops for all cells in the cost column with the following code inside:

If Range("B2").Interior.ColorIndex = 6 Then 'or whatever your shade color is
Range("B2").Formula = "hello" 'type whatever your formula is here
End If

Hope this helps!

"SMac" wrote:

I have 2 columns of concern DATE and COST.
I would like the COST cell to be shaded if the DATE cell is less than a
specificed date.
Then if it is possible to calculate the COST field but only the ones
highlighted.

Help is greatly appreciated!!

Thanks, Stacey

Use this: "=$A$1<DATE(2005,3,5)"

"SMac" wrote:

I copied your formula and it worked but I want to enter a date i.e. 3/1/05
but it doesn't seem to work, I have: =$A$1<"3/5/05"
Where A1 is 3/8/05.

I will look into the macro.

Thanks!

"Chad" wrote:

For the conditional format, use the following for the condition of the cost
column:

=$A$2<TODAY()
and so on, where cell A2 is the corresponding date column. Select the
appropriate shade on the pattern tab.

As far as calculating the cost column if it is highlighted, my only thoughts
are to write a macro that you can run that checks for all cost cells in the
column and performs the functions if the cell is shaded. Use an outside loop
that loops for all cells in the cost column with the following code inside:

If Range("B2").Interior.ColorIndex = 6 Then 'or whatever your shade color is
Range("B2").Formula = "hello" 'type whatever your formula is here
End If

Hope this helps!

"SMac" wrote:

I have 2 columns of concern DATE and COST.
I would like the COST cell to be shaded if the DATE cell is less than a
specificed date.
Then if it is possible to calculate the COST field but only the ones
highlighted.

Help is greatly appreciated!!

Thanks, Stacey

GREAT! That worked.
Thanks!

"Chad" wrote:

Use this: "=$A$1<DATE(2005,3,5)"

"SMac" wrote:

I copied your formula and it worked but I want to enter a date i.e. 3/1/05
but it doesn't seem to work, I have: =$A$1<"3/5/05"
Where A1 is 3/8/05.

I will look into the macro.

Thanks!

"Chad" wrote:

For the conditional format, use the following for the condition of the cost
column:

=$A$2<TODAY()
and so on, where cell A2 is the corresponding date column. Select the
appropriate shade on the pattern tab.

As far as calculating the cost column if it is highlighted, my only thoughts
are to write a macro that you can run that checks for all cost cells in the
column and performs the functions if the cell is shaded. Use an outside loop
that loops for all cells in the cost column with the following code inside:

If Range("B2").Interior.ColorIndex = 6 Then 'or whatever your shade color is
Range("B2").Formula = "hello" 'type whatever your formula is here
End If

Hope this helps!

"SMac" wrote:

I have 2 columns of concern DATE and COST.
I would like the COST cell to be shaded if the DATE cell is less than a
specificed date.
Then if it is possible to calculate the COST field but only the ones
highlighted.

Help is greatly appreciated!!

Thanks, Stacey

Chad,

How would I go about writing the code to looking for the shading and in turn
sum the cells that have it?

Thanks!
Stacey

"Chad" wrote:

For the conditional format, use the following for the condition of the cost
column:

=$A$2<TODAY()
and so on, where cell A2 is the corresponding date column. Select the
appropriate shade on the pattern tab.

As far as calculating the cost column if it is highlighted, my only thoughts
are to write a macro that you can run that checks for all cost cells in the
column and performs the functions if the cell is shaded. Use an outside loop
that loops for all cells in the cost column with the following code inside:

If Range("B2").Interior.ColorIndex = 6 Then 'or whatever your shade color is
Range("B2").Formula = "hello" 'type whatever your formula is here
End If

Hope this helps!

"SMac" wrote:

I have 2 columns of concern DATE and COST.
I would like the COST cell to be shaded if the DATE cell is less than a
specificed date.
Then if it is possible to calculate the COST field but only the ones
highlighted.

Help is greatly appreciated!!

Thanks, Stacey