Excel - Logic...IF-THEN

[starman0724]

I realize this is probably an overly easy question, but I'm frustrated with manuals, and the help screens. What I'm interested in is counting the cell background fill color red, yellow or green), (I've got the function to do that) IF a certain cell meets a particular criteria (such as after a particular date).

All the help screens thus far show me how to do IF-AND logic which returns a "true" or "false" value which has to be stored in another "table" then those results are tallied...very messy and easy to mess up the links accidentally.

Ken Starry

 

[VBslammer]

I'm having a hard time following what you're trying to do, but it sounds like you want to change a cell's backcolor depending on certain criteria. If that's what you're doing, Excel provides a 'Conditional Format' tool to do just that. Just select the desired range of cells, click on 'Format -> Conditional Formatting...' and set the font styles, colors and criteria - no programming required.

Good luck,


VBSlammer

 

[starman0724]

Thankyou for the tip on coloring the cells given certain criteria.

My problem is something like this:

I've got data from an external source which provides a condition "priority". This priority needs to be color coded (yes, the color of a traffic light...1=green, 2=yellow & 3=red).
A column with a date is also imported. We compare the imported date to a reference date and place an "X" in another column for easy reference of "updated" status.

Up to this point it is easy. But the next thing is to calculate totals of each color FOR DATA NEWER THAN THE REFERENCE DATE (I've been using a function
=IF(AND(E2=2,F2="X&quot,$N$25,"&quot
This is great, but it gives me columns, with a value of "1" (cell N25) which have to be summed and referenced in other places. I was hoping there would be a function similar to the count color funtion:
Function CountColor(rColor As Range, rSumRange As Range)

Dim rcell As Range
Dim iCol As Integer
Dim vResult

iCol = rColor.Interior.ColorIndex

For Each rcell In rSumRange
If rcell.Interior.ColorIndex = iCol Then
vResult = vResult + 1
End If
Next rcell

CountColor = vResult
End Function

Where it could do all the work for me in one fell swoop...meaning, it would count the colors IF the date in another column was newer than a reference date.

Ken Starry

 

[VBslammer]

It's hard to visualize someone else's spreadsheet layout, but if you're going to use custom functions then the sky is the limit - just modify what you have to check the reference date in addition to the backcolor.

Using named ranges:

=CountColor(GreenCell, ColorRange, DateCell)
=CountColor(YellowCell, ColorRange, DateCell)
=CountColor(RedCell, ColorRange, DateCell)

Using cell references:

=CountColor(G1, A1:A40, H1) ... or whatever cell ranges you're using.


I modified your function to check the cell to the right of the colored cell (which I'm calling the imported date) to see if it is greater than the reference date cell (rRefDate). You could add another argument specifying the number of columns to offset if you plan on calling this function from different sheets.


Function CountColor(rColor As Range, rSumRange As Range, rRefDate As Range) As Integer

Dim rCell As Range
Dim iCol As Integer
Dim intResult As Integer

iCol = rColor.Interior.ColorIndex

For Each rCell In rSumRange
If rCell.Interior.ColorIndex = iCol Then
If rCell.Offset(0, 1) > rRefDate Then
intResult = intResult + 1
End If
End If
Next rCell

CountColor = intResult

End Function


You can also use a database function to get the same results. The DCOUNT(database, field, criteria) function will work if it's set up right. Say you have:

ColA ColB
---------------------
Colors Dates
---------------------
1 1/1/02
---------------------
2 5/3/02
---------------------
1 8/12/02
---------------------
3 9/3/01
---------------------

You can name that range 'database' (A1:A5 - needs to include the headings).

Then you define your criteria ranges like this (headings included also):

ColR ColS
---------------------
Dates Colors
--------------------- Range = R1:R2
>4/1/02 1
---------------------
Dates Colors
--------------------- Range = R3:R4
>4/1/02 2
---------------------
Dates Colors
--------------------- Range = R5:R6
>4/1/02 3
---------------------

Then to get the totals for each color whose date meets the criteria (>4/1/02), use the DCOUNT():

TotalGreens =DCOUNT(database, "Colors", R1:R2) = 1
TotalYellows =DCOUNT(database, "Colors", R3:R4) = 1
TotalReds = DCOUNT(database, "Colors", R5:R6) = 0

You can set up the colors column with conditional formatting so that is has 3 conditions:

If value = 1, the backcolor and text color is green
If value = 2, the backcolor and text color is yellow
If value = 3, the backcolor and text color is red

That way all you see is the color, and you can still use the numeric value in your formulas.

Am I in the ball park yet?
VBSlammer