VBA for Formatting Worksheet

[khaos]

Hey guys,

I have a problem with formatting for one of my worksheets. The excel worksheet is already formatted but based on the data the formatted row colors expand in different cells. That's the ideal situation but my excel worksheet isn't doing this based on the macro once the data is imported. The formatting only carries forward up to a certain cell and then stops. Here's the code if someone can help me.


Workbooks(strCurBook).Worksheets("29)").Activate

'Query Data
cnTmpRec.Open "select Top 65000 * from Assets.GDP", cnTables, 3, 3

If cnTmpRec.RecordCount <= 0 Then
GoTo endCase
End If

'Copy Results
ActiveSheet.Range("B10").CopyFromRecordset cnTmpRec

'Format Cells
Cells(1, 1).Value = 1
Range("A1").Select
Selection.Copy
Range("B10").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlMultiply, _
skipblanks:=False, Transpose:=False
Cells(1, 1).Value = ""
Range("A1").Select

'alt. Colors
Range("B12:M13").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteFormats, operation:=xlNone, _
skipblanks:=False, Transpose:=False
Application.CutCopyMode = False

'Apply Conditional Formatting
Columns("G:M").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""No"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(Selection.FormatConditions.Count).Font
.Bold = True
.Italic = False
.Color = -16777024
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True

Range("A1").Select


Hope someone can help me out.

Thanks.

[someboddy]

Are G to M the only columns you want to conditionally format?

[khaos]

Nope I want the conditional formatting to run through all the columns. I know I have it as G:M there, because initially i specified those columns but I want them to run through all. Although the G:M is only an additional conditional formatting. The alt colors before it should go through all the columns. and not only till M. I have dates as headings to the columns with Sort Filters, I assume that's the reason why it isn't going through, as the Date format doesn't carry through too to the other columns.

[someboddy]

Try this. The part I've changed is underlined.

Workbooks(strCurBook).Worksheets("29)").Activate

'Query Data
cnTmpRec.Open "select Top 65000 * from Assets.GDP", cnTables, 3, 3

If cnTmpRec.RecordCount <= 0 Then
GoTo endCase
End If

'Copy Results
ActiveSheet.Range("B10").CopyFromRecordset cnTmpRec

'Format Cells
Cells(1, 1).Value = 1
Range("A1").Select
Selection.Copy
Range("B10").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlMultiply, _
skipblanks:=False, Transpose:=False
Cells(1, 1).Value = ""
Range("A1").Select

'alt. Colors
Range("B12:M13").Select
Selection.Copy
ActiveSheet.UsedRange
Range("B12").select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.PasteSpecial Paste:=xlPasteFormats, operation:=xlNone, _
skipblanks:=False, Transpose:=False
Application.CutCopyMode = False

'Apply Conditional Formatting
Columns("G:M").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""No"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(Selection.FormatConditions.Count).Font
.Bold = True
.Italic = False
.Color = -16777024
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True

Range("A1").Select

[khaos]

I tried this code, and it works fine. Although the column headers formatting doesn't carry through all the way. It stops at Column M for some reason. As I mentioned before this may be because of the date format/sort filter on the headings. So I'm not sure what I should do. Your code did solve part of my problem though. Thanks.

[khaos]

I've specified the column names from G:M in the additional conditional formatting. How can i specify the column range from G to whatever depending on the data instead of specifying actual column names. This will help me a great deal.

[someboddy]

Workbooks(strCurBook).Worksheets("29)").Activate

'Query Data
cnTmpRec.Open "select Top 65000 * from Assets.GDP", cnTables, 3, 3

If cnTmpRec.RecordCount <= 0 Then
GoTo endCase
End If

'Copy Results
ActiveSheet.Range("B10").CopyFromRecordset cnTmpRec

'Format Cells
Cells(1, 1).Value = 1
Range("A1").Select
Selection.Copy
Range("B10").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlMultiply, _
skipblanks:=False, Transpose:=False
Cells(1, 1).Value = ""
Range("A1").Select

'alt. Colors
Range("B12:M13").Select
Selection.Copy
ActiveSheet.UsedRange
Range("B12").select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.PasteSpecial Paste:=xlPasteFormats, operation:=xlNone, _
skipblanks:=False, Transpose:=False
Application.CutCopyMode = False

'Apply Conditional Formatting
Range("G1").select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).EntireColumn.Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""No"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(Selection.FormatConditions.Count).Font
.Bold = True
.Italic = False
.Color = -16777024
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True

Range("A1").Select

[khaos]

Thanks someboddy your code works like a charm. I figured it out as well, but your code does it in a simpler format. Although the date headings format doesn't carry throughout, I haven't been able to fix that. I have Sort Filter drop down menus below the headings & that formatting doesn't carry onto the other columns as well. Well I'll have to do with this for now. Thanks for all your help.

[someboddy]

Those filter drop-downs(AutoFilters) are not formatting, so you can't copy them like with format copy. Just add this at the end of the function:

Range("B10").select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.AutoFilterMode = False
Selection.AutoFilter

I can't test it right now, but it should do the trick. I assume that you want all columns starting from B to have AutoFilters, and that the drop-downs should be on row 10. If that's not the case - just change the cell selected in the first command.

[khaos]

Hey someboddy,

sorry it took me a while to respond, but thanks for your help. your code solves my problem. I just proceeded by taking out the autofilters and forgot about the code. But now i tried your code and it works like a charm. Thanks a lot for all your help.

I have trouble with another workbook. it has more to do with copy/pasting data into another worksheet, my code is doing it all wrong. I'm still trying to figure out how I want to do it, but I hope you have some ideas for my code.
Thanks a lot for all your help!.