.FontSize conditionally

[John C]

I have cells that are conditionally formatted as follows:
=$D6>=VLOOKUP($B6,irr_goals,2)

Bold, Underline

I want these cells to increase the fontsize to 10 under the same conditions.
Does anybody know why Microsoft has the Font option Greeked out??

Even more important, can somebody tell me how to make this work...I know
there is a .fontsize parameter in code so it stands to reason that this can
be done, but I don't know VBScript.

Please Help

 

[John Bundy]

This site has been recommended several times:

http://www.xldynamic.com/source/xld.CFPlus.Download.html

Talks about you issue in the first section. Let me know how it works if you
use it.

 

[Gord Dibben]

Note: CFPlus will allow Font Size changes in Condtional Formatting unlike the
Excel default CF.


Gord Dibben MS Excel MVP

 

[John C]

This appears to solve my problem but I can't test it until I get permission
from corporate to download the add-in (ahahaha). It will solve my problem as
long as the following condition is true:

If I download the Add-in, and use it to conditionally format the font size,
it has to embed vbscript so that any excel computer can process the results
without having to download the add-in. It is not feasible for me to put the
add-in on all of the computers that need this report.

I don't see how else an add-in could work though, so assuming that this is
indeed the case then you guys solved my problem. I will post any further
questions that may stem from this, and thank you for the help.

 

[Gord Dibben]

If all you want to do is increase the font size for Conditionally Formatted
cells only you could run a macro.

Sub Font_Increase()
With ActiveSheet.UsedRange.SpecialCells _
(xlCellTypeAllFormatConditions)
.Font.Size = 10
End With
End Sub

Or tie it to calculate event code.

Private Sub Worksheet_Calculate()
With ActiveSheet.UsedRange.SpecialCells _
(xlCellTypeAllFormatConditions)
.Font.Size = 10
End With
End Sub


Gord Dibben MS Excel MVP

 

[John C]

OK This looks like what I need but how do I embed this to work automatically?
I have the cells with Conditional Formatting>Function Is>
=$D6>=VLOOKUP($B6,irr_goals,2,false)

BOLD, UNDERLINE

Where within all of that would I put the font_increase() function to make
the cell value appear as font size 10?

 

[Gord Dibben]

John

To work automatically you would use the second set of code which is event code.

Right-click on the sheet tab and "View Code"

Copy/paste the Private Sub Worksheet_Calculate() code into that module.

Whenever the sheet calculates, the CF cells will update with fontsize of 10.

Leave your BOLD, UNDERLINE CF as is.

Note: as written the code looks for CF cells in the used range.

If you wish to narrow that range you could change the code to

Private Sub Worksheet_Calculate()
With ActiveSheet.Range("A1:C50").SpecialCells _
(xlCellTypeAllFormatConditions)
.Font.Size = 10
End With
End Sub


Gord

OK This looks like what I need but how do I embed this to work automatically?
I have the cells with Conditional Formatting>Function Is>
=$D6>=VLOOKUP($B6,irr_goals,2,false)

Where within all of that would I put the font_increase() function to make
the cell value appear as font size 10?

Gord Dibben MS Excel MVP