Page 1 of 3 123 LastLast
Results 1 to 15 of 45

Thread: Color me blue

  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi! I want to ask a 'fun' question.
    I am running code to output queries result into Excel file.
    All fields that are blank should be highlighted in let say blue color.
    I expect one blank field per row/column. It should be colored.

    I am running a macro AND VBA code, so it can be done in any format.

    Thanks

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    What code are you using to ouptput the values?

    If it only outputs certain places, and just leaves some cells blank, then I would start with changing the color of the the affected cells to blue, and when the code puts a value in a cell to remove the color.

    Or do it opposite if you are removing entries (clear all and make blue when "blanked")

    Another option is at the end of hte code, select the blanks in the region of interest and turn them blue

    Additionally you could use conditional formatting in the cells to make blank cells blue. This will work after the fact as well to make cells blue when values are deleted and remove the blue when values are added, all automatically.

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, Steve!

    Here is the deal...

    I am going to be using following code to create an output in Excel:

    With rst2
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open "[Report_Queries]", cnn1
    End With
    strReportName = varCombo1 & "Recruitment Audit Report.xls"
    Debug.Print strReportName
    If Dir(strReportName) <> "" Then
    'Debug.Print strReportName
    Kill (strReportName)
    End If

    'Loop thru queries
    Do While Not rst2.EOF

    strTemp = rst2.Fields("QueryName")

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTemp, strReportName


    Set cmd1 = Nothing

    rst2.MoveNext
    Loop
    rst2.Close
    -----------------------------------------------------------------------------------------------------------------


    Report_Queries is the table that containing queries to run.

    When all said queries are ran - I have Excel file with tab-per-query.

    It is normally

    ID Field1 MissingValues

    12_______ABC_______[_____]
    15_______ABB_______[_____]
    20_______AAA_______[_____]

    I am only displaying it for people to see what they have to fix.
    So in illustrated case Missing Values should have 3 cells blue. No removal afterwards, no data entry - just visual effect to 'not to miss'!!!

    Thanks so much

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You need to use Automation to run Excel from Access. For example:
    Code:
    Dim xlApp As Object
    Dim xlWbk As Object
    Dim xlWsh As Object
    Set xlApp = CreateObject(Class:="Excel.Application")
    Set xlWbk = xlApp.Workbooks.Open(Filename:=strReportName)
    For Each xlWsh In xlWbk.Worksheets
      xlWsh.UsedRange.FormatConditions.Add(Type:=1, _
    	Operator:=3, Formula1:="=""""").Interior.ColorIndex = 34
    Next xlWsh
    xlWbk.Close SaveChanges:=True
    xlApp.Quit
    Set xlWsh = Nothing
    Set xlWbk = Nothing
    Set xlApp = Nothing

  5. #5
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello, Hans! And thanks so much as always.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='MS_fan' post='763639' date='05-Mar-2009 16:09']Only thing is how to have all colored cells separated with grid vs solid rectangular and fonts MS Sans Serif 10 autofit so all the columns are as wide as they should be? Thanks[/quote]

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try
    Code:
    For Each xlWsh In xlWbk.Worksheets
      xlWsh.UsedRange.FormatConditions.Add Type:=1, _
    	Operator:=3, Formula1:="="""""
      With xlWsh.UsedRange.FormatConditions(1).Borders
    	.LineStyle = 1
    	.Weight = 2
    	.ColorIndex = -4105
      End With
      xlWsh.UsedRange.FormatConditions(1).Interior.ColorIndex = 34
    Next xlWsh

  8. #8
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts
    YAHOO! THANKS A MILLION!!!!!!!!

  9. #9
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi again...
    I was trying on my own to have autofit for the columns, bold headers and I've not gotten far:

    xlWsh.UsedRange.FormatConditions(1).Select
    xlWbk.Rows ("1:1")
    selection.Font.Bold = True
    cells.EntireColumn.AutoFit
    xlWsh.Rows("1:1").RowHeight = 20.25
    ActiveWorkbook.Save

    It stoped me at xlWsh.UsedRange.FormatConditions(1).Select

    What am I doing wrong, please, tell. Thanks

  10. #10
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='MS_fan' post='763708' date='05-Mar-2009 19:38']It stoped me at xlWsh.UsedRange.FormatConditions(1).Select[/quote]
    xlWsh.UsedRange.FormatConditions(1) is the first conditional format that you see in the Format > Conditional Formatting dialog box, you can't select this.

    Try something much more simple like
    xlWsh.UsedRange.Columns.Autofit

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Stuart has already posted the solution, but I wanted to point out that the lines

    selection.Font.Bold = True
    cells.EntireColumn.AutoFit

    and

    ActiveWorkbook.Save

    will cause serious problems. Because they do not refer directly or indirectly to one of the Excel object variables used in the code, they will cause an extra, invisible instance of Excel to be created. This instance will remain in memory when the procedure ends, taking up computer meomory and probably locking the worksheet.
    When using Automation, you should ALWAYS refer back to the object variables, for example

    cells.EntireColumn.AutoFit > xlWsh.Columns.AutoFit

    and

    ActiveWorkbook.Save > xlWbk.Save

  12. #12
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, Stuart!!!

    Hans, I am so surprised! Many years ago I was using VBA a lot and one of my main problem was that Excel hang up and I was going to Task Manager to shot it down.
    I searched everywhere and there were questions and no answers. Now I got it and it is so cool!!!

    I want to ask - I am getting more formatting requests and I use to record macro and then alter a bit and it worked.
    Nowadays I am recording and trying to adopt into the code you gave me and it doesn't fit...

    How do I find correct properties to reffer to when I need to
    center the text in columns
    inderline A row
    coloring headers where I have between 2-5 cells in row A to color
    etc...

    Thanks so much for your help!

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can record a macro in Excel for that, then adapt the code as follows: a recorded macro will usually refer to the Selection, i.e. the cell(s) selected by the user.
    In your code, replace Selection with a reference to a range on your worksheet, for example

    Selection.EntireColumn.AutoFit

    becomes

    xlWsh.UsedRange.EntireColumn.AutoFit

    and

    Selection.Font.Bold = True

    becomes

    xlWsh.Range("A1:F1").Font.Bold = True

  14. #14
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, I had tried. Some successfull!

    I haved another brain teaser.

    My queries are missing data in each column in different cells in Excel.

    I was asked to summarize each field and give a summary per ID.

    I took each table apart in Access. Wrote tons of queries and built summary table.

    After I had seen how easily you colored those empty cells for me I have a crazy idea.

    Is there a way (maybe) to have sum of the colored field (thus Formula:"""") by column and also by row for my Summary WKsheet?

    ID__________Col________Col2
    12__________we__________[]
    13__________[]___________[]
    14__________[]___________[]

    Total:_______2____________3

    Thanks

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='MS_fan' post='764664' date='10-Mar-2009 19:27'][/quote]
    You can use the COUNTBLANK function for this: for example if you have data in A1:A10, you could enter the formula

    =COUNTBLANK(A1:A10)

    in A11. Using code this would be

    xlWsh.Range("A11").Formula = "=COUNTBLANK(A1:A10)"

    Similar for the count of blank cells in a row: say you have data in A2:H2. You could place the following formula in I2:

    =COUNTBLANK(A2:H2)

    Using code:

    xlWsh.Range("I2").Formula = "=COUNTBLANK(A2:H2)"

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •