excel- Coloring cells with missing data 1

[tav1035]

I have a spreadsheet with the following data->

Wo# Description Need Date Start Date
1002 fix lights 1-10-2008 1-8-08
1003 change filters 2-5-2008 2-4-08
1004 move totes 2-6-08
1005 relamp 3-1-2008
1006 repair cord 3-5-08

I would like to color the cells that are missing dates "Yellow".

I did this with conditional formatting, (cell value is equal to ="")but every cell after row6 is blank, so my "C" and "D" column after my data ends all turn "Yellow".

I would like to end my conditional formatting at the last row that has data.

Sub emptyCells()
    Columns("C:C").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="="""""
    Selection.FormatConditions(1).Interior.ColorIndex = 6
 
End Sub

I need to use macros to build the conditional formatting.


Thanks,
tav

 

[AnotherHiggins]

-> I need to use macros to build the conditional formatting.

Actually you don't. You just need to change the formula in Conditional Formatting. (But, FYI, if you did need a macro, you should post VBA questions in forum707)

Let's say you know column A will always be populated for every single record. Then you could go to Conditional Formatting, change the first box to Formula Is and type in something like
[tab][COLOR=blue white]=And($A1 <> "", $C1 = "")[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[tav1035]

anotherhiggins,
Thanks that worked and I just recorded it in a macro.
tav

 

[GlennUK]

Tav,

why do you need a macro to build the conditional formatting?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[tav1035]

GlennUK,
I knew the question would come up.
We have maintenance software that we can run SQL queries in. The results shows up sort of like a spreadsheet view.
We can then click a download button and it exports to an excel spreadsheet as a .csv file. When it exports the data is normalized but it exports alot of fields. Since the excel spreadsheet is spawned from the download button, it is always a new and randomed named file. At this point we use macros to clean up and organize the spreadsheet as a report..
Instead having a report writer to write a different report for every type of analysis, we can run a different macro to get a different analysis on the same download.

Some of the different macros would, in this case find work orders that has data integrity problems (missing important data necessary to get the jobs done on time).

So Analysis can run a SQL query for things like past due work and run a macro to clean it up and add formulas to subtract the days between Due Date and todays date.

This makes it the different analysis to produce there on reports.

tav

 

[SkipVought]

"Since the excel spreadsheet is spawned from the download button, it is always a new..."

NEW is your problem. IMPORT rather than OPEN.

Data > Import... into the SAME workbook. Then spawn. The CF issue goes away!

I use this general technique regularly. It enhances maintenance and control.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[tav1035]

Skip,
I use alot of imports too and also odbc into templates However, I would have to create a template for every report. Doing it this way allows me to compile macro on top of others like modules. A user can run one macro for past due report and send it out, the that same report could have another macro run against it to seperate work order based on who its assigned to, then hand them out seperately.
thanks guys,
tav