Change the font color of the column heading

[polavar]

I need macro which will automatically change the font color of the heading when there are no records for that column.

Thank You

 

[DaleWatson123321]

Hi polavar,

It's possible to use VBA but VBA isn't required. You could use Conditional Formatting, as follows...

Based on a heading being in B1, use the following steps:

1) Place the cursor on cell B1, and from the menu, use: Format - Conditional Formatting.

2) Change "Cell Value Is" to "Formula Is"

3) Enter this formula: =COUNTA(B:B)-1=0

4) From within the Conditional Format Window, choose: Format... - Font - and Color.

After finishing with cell B1, you can then copy this formatted cell to the other headings. Use: Edit - Paste Special - Formats.

Hope this helps. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[polavar]

I tried. it is working but it is changing the font color of the content in the first row. in my application, as first line is generic line and second line contains header. I wanted to change the color of the font for the content in the secod row.

Thank You

 

[DaleWatson123321]

Hi polavar,

The following formula will work, based on there being a (generic) value in the first row, and your header on the second row.

=COUNTA(B:B)-3=0

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[polavar]

Hi Watson,

It is working . Thank You very much for your help. But I need macro for this function. I try the following code, but I am getting Syntax error. Could you please help me in fixing this.


sub Blank_Column()

max_columns = 100 'maximum columns that you expect to have in the sheet

j = 1
Do While j < max_columns
Selection.FormatConditions.Delete
If Selection.FormatConditions.Add _ Type:=xlExpression, Formula1:= &quot;=COUNTA(j:j)-2 = 0&quot; Then


Cells(2, j).Interior.ColorIndex = 6
Exit Do
End If
j = j + 1
Loop

End Sub

 

[polavar]

Hi watson. I got it. I used following code.

Sub Blank_Column()
'
' Blank_Column Macro
' To locate columns with out Data
'

Range(&quot;A2&quot.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
&quot;=COUNTA(A:A)-1=0&quot;
Selection.FormatConditions(1).Font.ColorIndex = 3
Selection.Copy
Range(&quot;B2:BP2&quot.Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range(&quot;C2&quot.Select
Application.CutCopyMode = False
End Sub


Thank You very much for your help.

 

[polavar]

Hi Watson,
Again I need help from you.

Sector Region Country
Telecommunication USA USA
Telecommunication USA Canada
Transport Africa Kenya
Energy USA W.Indies
Finance Aus Aus
Finance Aus NZ
Commerce USA USA
Textiles USA Canada
Public sector Aus Aus
Aviation Europe France
Aviation Europe Germany
Agriculture Asia India
Agriculture Asia China
Technology USA USA

from the above table I wanted to filter the records where 'Secrtor' mapped to one region but 2 or more countries.

For Eg: from the above table I wnated to filter 'Telecommunication', Finance, Aviation, Agriculture etc.

Thank You in advance

 

[polavar]

Need help in conditional formatting

Sector Region Country
Telecommunication USA USA
Telecommunication USA Canada
Transport Africa Kenya
Energy USA W.Indies
Finance Aus Aus
Finance Aus NZ
Commerce USA USA
Textiles USA Canada
Public sector Aus Aus
Aviation Europe France
Aviation Europe Germany
Agriculture Asia India
Agriculture Asia China
Technology USA USA

from the above table I wanted to filter the records where 'Sector' mapped to one region but 2 or more countries.

For Eg: from the above table I wnated to filter 'Telecommunication', Finance, Aviation, Agriculture etc.

Thank You in advance