Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

VB script to do conditional formatting of font size

I've tried to do a little VB scripting.
I need to do some conditional formatting of the font size.
If cell Bx has no value, cell Cx must have another font size then originally set.
All cells in coloumn B must be checked from B4 to B1007, and cell C formatted accordingly!
What I've tried to do is this;

Sheets("Sheet2").Select
Dim Check As Boolean
endrange = Range("B1007").End(xlUp).Row

For i = 1 To endrange

Check = Range("B" & i).Value Like ""

If Check = True Then

Range("C" & i).Select
With Selection.Font
.Name = "Arial"
.Size = 12

End With
End If
Next i
Any suggestions ?


Post your answer or comment

comments powered by Disqus
I have a sheet that has 3 rows and 20 columns grouped in blocks of 9
(the attached sheet will describe morein detail)

This is a Drag Racing Team Points and Ranking sheet (large portions were removed because of size limitations)

Group 1 is Best ET and it has 3 rows Best ET1, 2 and 3. there are 3 columns for Visit 1, same for Visit 2, 3, 4 and 5
I am trying to make a VB Script to only allow 1 selection per group, forinstance if I enter a 1 in row 1 column 2 the (Best ET 1) will highlight and then Lock all blank cells in Visit 1 column, and the entire Best ET 1 Row through visit 5. This says that you used up your
first Best ET points (you are allowed a total of 3 for the season)

this is repeted for Group 2 (Best 60ft), Group 3 (Best light)

Any Ideas on how to make this?

I have a workbook that requires a VB code to help me out with Conditional Formatting. I use Office 2003 which is restricted to 3 conditions, I know there is an add in I can use that would help me do this but other people may use this that wont have the add in. I have decided to use VB if possible to get this done.

I have a range of cells from B22 – T22 in these cells I will be putting codes, when these codes are put into the cells I would like the cells to shade a different colour depending on what code I use. Here is an example of what I mean.

P = blue

S = red

HL = green

ML = magenta

FL = orange

I may have a couple of more codes I will add at a latter time. Is it possible for VB code to do this?

Hi,

I have the following problem.

A1= B1+B2 = 1
A2 = B2 + B3 = -2

If ABS(A2)>A1, A2 text will turn red.

I tried using conditional formula to do something like =IF(ABS(A2)>A1), change Font colour to red. but it didn;t work...

Hi I have just created a worksheet with 6 columns a1 through a6....I have used the available VBA script to do conditional formatting for 6 colours in a7....it checks the value in a1 and depending on certain boundries sets a7 to a colourI now need to do something the same with a8.....depending of the colour of a7 I need to write that colour in a8......any clues how I do this...?????...can I do it in the same vba as in a7 or do I need new vba..???

VBA code below....

Private Sub Worksheet_Change(ByVal Target As Range)

Dim iColor As Integer

    If Not Intersect(Target, Range("J5")) Is Nothing Then

        Select Case Target

            Case 1 To 15

                iColor = 3
                                      
            Case 17 To 29

                iColor = 4
                         
                
            Case 30 To 35

                iColor = 23
                  
                      Case Else

                 iColor = 6
                        

        End Select
        

        Target.Interior.ColorIndex = iColor

    End If


End Sub


I have a column graph that has 10 columns.

The range of scores on the Y-axis ranges from 1 to 100.

I would like the columns to automatically color themselves depending on the result.

For example - if a column gets a score of 39 or less - red. 40 to 59 is yellow and 60 and above is green.

Is there anyway to do this?

I know how to do conditional formatting of cells in Excel - but not of graphs.

Many thanks,

Carla

Hello all,

I have what seems like an easy problem but I cannot seem to figure out what to do here. I've been able to set conditional formatting by code in order to change font and fill patterns, but I can't seem to change number format. I'd rather not add any additional helper columns if I can avoid it.

I want to set the format of cells in column J to ZIP code only if the adjacent cell in column K is blank. I tried recording a macro and got this:

	VB:
	
Cells.FormatConditions.Delete 
Range("J2:J114").Select 
Selection.NumberFormat = "00000" 
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=K2=""""" 
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority 
ExecuteExcel4Macro "(2,1,""00000"")" 
Selection.FormatConditions(1).StopIfTrue = False 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When I run the code, it of course sets all cells in the selection to format "00000" and no conditional formatting is actually created. I've never seen ExecuteExcel4Macro and the code errrors out here. Is there some method to change number format which maybe looks like:

	VB:
	
 Selection.FormatConditions(1).NumberFormat = "00000" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Many thanks in advance!

Hi everyone

I was hoping you could help me with some VB code for conditionally formatting a column of cells...

I have attached a dummy workbook, and basically the values in column F are in white. I would like these values to turn to black font, but only after all the corresponding values in column H have been filled in (between 1-5).

I have a macro in my actual workbook, which is why I need it as code and not a formula in the cell.

Thanks for your help!!

DP

I have 2 columns 'C' and 'D' which I want to apply Conditional Formatting to (i.e. colour the background of the cell in column 'D' for the respective row in colum 'C') if they contain different values.

I have the following code:

	VB:
	
 CellCCondFormatting() 
    Dim j As Long 
    Range("C2").Select 
    j = Range("C2").CurrentRegion.Rows.Count 
    MsgBox ("1st: " & j) 
    Range("C2:C" & j).FormatConditions.Delete 
    Range("C2:C" & j).FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ 
    Formula1:="=D" & j 
    Range("C2:C" & j).FormatConditions(1).Interior.ColorIndex = 3 
    Range("C2:C" & j).FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _ 
    Formula1:="=D" & j 
    Range("C2:C" & j).FormatConditions(2).Interior.ColorIndex = 4 
    MsgBox ("2nd pass: " & j) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
It iterates through all rows in my CurrentRegion OK but the Conditional Formatting 'formula' operates on the wrong value in column 'D'. For example, when viewed via menu option Format > Conditional Formatting... row 2 column 'D's Conditional Formatting value is D1714, row 3 column 'D's value is 'D1715' and so on ....

Row 1714 is the last row number in my CurrentRegion however my CurrentRegion will change every time I run the macro so cannot use a 'fixed' row number.

Ques: How do I iterate through an unknown number of rows and apply Conditional Formatting to respective rows, e.g. cell 'D2' for row 2, 'D3' for row 3, 'D4' for row 4 etc. ?

Thank you for any assistance in advance.

Kind regards,
Andy
PS: I have tried to adhere to all new thread posting rules :-) Auto Merged Post Until 24 Hrs Passes;

I've answered my own question by changing:

	VB:
	
Range("C2:C" & j).FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ 
Formula1:="=D" & j 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
to...


	VB:
	
Range("C2:C" & j).FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ 
Formula1:="=RC[1]" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Auto Merged Post Until 24 Hrs Passes;

I've answered my own question; I've changed:

	VB:
	
Range("C2:C" & j).FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ 
Formula1:="=D" & j 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
to...


	VB:
	
Range("C2:C" & j).FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ 
Formula1:="=RC[1]" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I used the first block of code below to do conditional formatting for the bars of the charts and it works. I am trying to apply this same coding to the ticklabels, but it's not working. How can I apply the same conditional formatting to Tick Labels and Data Labels?


	VB:
	
 ActiveChart.SeriesCollection(1) 
    val = .Values 
    For x = 1 To .Points.Count 
        If val(x) > 3 And val(x) < 10 Then 
            .Points(x).Interior.ColorIndex = 16 
        End If 
    Next x 
End With 
 
ActiveChart.Axes(xlCategory).Select 
Selection.TickLabels.AutoScaleFont = False 
With Selection.TickLabels.Font 
    val = Category 
    For x = 1 To .Points.Count 
        If val(x) > 3 And val(x) < 10 Then 
            .Font.ColorIndex = 16 
        End If 
    Next x 
End With 
 
ActiveChart.SeriesCollection(1).DataLabels.Select 
Selection.AutoScaleFont = False 
With Selection.DataLabels.Font 
    val = Data Value 
    For x = 1 To .Points.Count 
        If val(x) > 3 And val(x) 10 Then 
            .Font.ColorIndex = 16 
        End If 
    Next x 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you.

Greetings, all -

I am creating a simple spreadsheet template to be distributed to users
for data entry purposes. The first column contains the names of the
desired data elements. The second column contains the definition for
each data element. The definitions are formatted in red text. I intend
for the user to remove the definition and replace it with the proper
information for each element. For example:

Data Element Definition
1.0 LastName The last name of the person to whom questions
about the sample should be directed.

The user would replace the definition with, say, "Smith" using the usual
method for overwriting data in a cell (i.e. click'n'type). Once the
definition has been overwritten and the user hits Enter, I would like
for the font color to change from red to black. Here's the rub...this
has to be done without macros or VB of any kind. I'm hoping to use
Conditional Formatting combined with some native formula that I have yet
to identify; something like the "IS" functions, perhaps? Alas, if only
there was such a thing as ISNEW()...

Ideas?

tia,

LeAnne

Greetings, all -

I am creating a simple spreadsheet template to be distributed to users
for data entry purposes. The first column contains the names of the
desired data elements. The second column contains the definition for
each data element. The definitions are formatted in red text. I intend
for the user to remove the definition and replace it with the proper
information for each element. For example:

Data Element Definition
1.0 LastName The last name of the person to whom questions
about the sample should be directed.

The user would replace the definition with, say, "Smith" using the usual
method for overwriting data in a cell (i.e. click'n'type). Once the
definition has been overwritten and the user hits Enter, I would like
for the font color to change from red to black. Here's the rub...this
has to be done without macros or VB of any kind. I'm hoping to use
Conditional Formatting combined with some native formula that I have yet
to identify; something like the "IS" functions, perhaps? Alas, if only
there was such a thing as ISNEW()...

Ideas?

tia,

LeAnne

Project1.jpg

Hi,

I have set up a simple spreadsheet which shows me how many staff I have on at any particular time. I require to shade boxes so I can see how many staff there are so for example if I have 2 staff on, 2 boxes in the column should be shaded. I have been trying to do conditional formatting for this but the only way I can get it working is to format each cell indiviually which is extremely time consuming. Is there a way to paste the conditional format but amend it automatically.

Not sure I have explained this very well so have attached an image of the spreadsheet. The value in row 2 is the number that gets changed and hopefully the shaded cells in the colums will correspond with that number.

Thanks

I'm using excel 2003 and wanted to know if it is possible to do conditional formatting based on the reference values of other cells. In the attached document, can I make columns Z, AA and AC shaded to the same color as AB?

My goal is to select this entire week of cells and just drag it down for a year.

Hello All,

I use the following code below to let the user double click to get a "Check Mark" in a specified range of cells. However, when I try to protect the spreadsheet, this macro doesn't seem to run and gives me this error "Unable to set name properly of Font Class". Is there a work around to get this to work with the Spreadsheet protected?

Kindly help.


	VB:
	
) 
     'Limit Target count to 1
    If Target.Count > 1 Then Exit Sub 
     'Isolate Target to a specific range
    If Intersect(Target, Range("myChecks")) Is Nothing Then Exit Sub 
     'set Target font tp "marlett"
    Target.Font.Name = "marlett" 
     'Check value of target
    If Target.Value  "a" Then 
        Target.Value = "a" 'Sets target Value = "a"
        Cancel = True 
        Exit Sub 
    End If 
    If Target.Value = "a" Then 
        Target.ClearContents 'Sets Target Value = ""
        Cancel = True 
        Exit Sub 
    End If 
End Sub 
 
[B][/B] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Booo

Hi,
I'm trying to add conditional formatting to my worksheet using a macro.
The condition is:
Look at all cells in Column B
If the cell value equals "Capacity" then change the background color of that cell and the rest of the cells in that row (e.g. from Column B to O).
E.G. if cell B6 = "Capacity" then change the background color of cells B6 to O6 to light blue
If cell B7 = "Availability" then change the background color of cells B7 to O7 to red

This should apply to all cell in column B and change the background color of appropriate cells.

The code I have so far is

	VB:
	
 ColourConditioning() 
     '
    Dim rngToFormat As Range 
    Set rngToFormat = ActiveSheet.Range("B6:B100") 
     'first, clear any old Cond format for these cells
    rngToFormat.FormatConditions.Delete 
     'add cond 1: if = "Capacity"
    rngToFormat.FormatConditions.Add Type:=xlCellValue, _ 
    Operator:=xlEqual, Formula1:="Capacity" 
    rngToFormat.FormatConditions(1).Interior.ColorIndex = 34 
     'add cond 2: if = "Availability"
    rngToFormat.FormatConditions.Add Type:=xlCellValue, _ 
    Operator:=xlEqual, Formula1:="Availability" 
    rngToFormat.FormatConditions(2).Interior.ColorIndex = 3 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This works to a certain extent but only changes the background color of the cell in column B, but should change Column B to O

See attachment
Can anyone help please

Andy

Hi all,

I've spent some time trying to get a workbook I had working perfectly in Excel 2010 with many conditional criteria, working correctly in Excel 2003. This is frustrating trying to reverse engineer the file but some of the users of the file will only have 2003 ...

I managed to get conditional formatting (covering font colour, fill colour, font format) working via VBA rather than using the standard 3 rules available in Excel 2003 (which are not enough for my needs). This works perfectly when saved as a 2003 .xls file within my Excel 2010, but opened in actual Excel 2003, the code gets stuck in an endless loop and I can't work out why!

Anyone care to take a look and point out what I hope is an obvious error? The "If, ElseIf, Else, End" structure shouldn't get stuck looping as far as I know? Code follows below.

Many many thanks for any help!
Stewstews


	VB:
	
 CondtionalFormats() 
     'Ranges - actual workbook has 10+, this is a sample of 2.
    Set GoodAvail = Range("$Q$3") 
    Set BadAvail = Range("$Q$5") 
     
    Dim mavail As Range 
    Dim yavail As Range 
     
     'Colours done this way to ensure am using Excel 2003 standard colours even if opened in 2010.
    ThisWorkbook.Colors(2) = RGB(255, 255, 255) 'White
    ThisWorkbook.Colors(10) = RGB(0, 128, 0) 'Green
    ThisWorkbook.Colors(40) = RGB(255, 153, 0) 'Orange
    ThisWorkbook.Colors(1) = RGB(0, 0, 0) 'Black
    ThisWorkbook.Colors(3) = RGB(255, 0, 0) 'Red
     
     'This is the first "If, ElseIf, Else, End" that starts endless looping - it's preceeded by 6 previous that work
correctly, and they look identical to me.
     'Availability Monthly Conditional Format
    For Each mavail In Range("Q13:Q631").Cells 
         'Colour it White
        If mavail.Offset(0, -16).Value = "" Then 
            mavail.Interior.ColorIndex = 2 
            mavail.NumberFormat = "0%; " 
             'Colour it Green with hidden text
        ElseIf mavail.Value >= GoodAvail And mavail.Value = "1" Then 
            mavail.Interior.ColorIndex = 10 
            mavail.NumberFormat = ";;; " 
             'Colour it Green
        ElseIf mavail.Value >= GoodAvail Then 
            mavail.Interior.ColorIndex = 10 
            mavail.NumberFormat = "0%; " 
             'Colour it Orange
        ElseIf mavail.Value > BadAvail.Value And mavail.Value < GoodAvail.Value Then 
            mavail.Interior.ColorIndex = 40 
            mavail.NumberFormat = "0%; " 
             'Colour it Red
        ElseIf mavail.Value = GoodAvail And yavail.Value = "1" Then 
            yavail.Interior.ColorIndex = 10 
            yavail.NumberFormat = ";;; " 
             'Colour it Green
        ElseIf yavail.Value >= GoodAvail Then 
            yavail.Interior.ColorIndex = 10 
            yavail.NumberFormat = "0%; " 
             'Colour it Orange
        ElseIf yavail.Value > BadAvail.Value And yavail.Value < GoodAvail.Value Then 
            yavail.Interior.ColorIndex = 40 
            yavail.NumberFormat = "0%; " 
             'Colour it Red
        ElseIf yavail.Value

First of all, thanks for posting the code that allows me to do conditional formatting for more than three colors.

I'm trying to get that code to work. In each of the cells within the identified range I have a simple division formula based on values contained in other cells. When I change values in those other cells, thus affecting the division, I'm wanting the color to change as well. I'm observing that in order for the formatting color to change, I need to F2 the division cell. Any ideas? Here is an example of the code:


	VB:
	
 Range) 
    Dim icolor As Integer 
     
    If Not Intersect(Target, Range("B4,B9,B14,B19,B24,B29,B34,B39,B44,B49,B54,B59,B64,B69,B74,B79,B84,B89,B94,B99")) Is
Nothing Then 
        Select Case Target 
        Case 0 To 0.099999 
            icolor = 3 
        Case 0.1 To 0.199999 
            icolor = 6 
        Case 0.2 To 0.299999 
            icolor = 10 
        Case 0.3 To 1 
            icolor = 5 
        Case Else 
            icolor = 2 
        End Select 
         
        Target.Interior.ColorIndex = icolor 
    End If 
     
    If Not Intersect(Target, Range("C4,C9,C14,C19,C24,C29,C34,C39,C44,C49,C54,C59,C64,C69,C74,C79,C84,C89,C94,C99")) Is
Nothing Then 
        Select Case Target 
        Case 0 To 0.199999 
            icolor = 3 
        Case 0.2 To 0.299999 
            icolor = 6 
        Case 0.3 To 0.399999 
            icolor = 10 
        Case 0.4 To 1 
            icolor = 5 
        Case Else 
            icolor = 2 
        End Select 
         
        Target.Interior.ColorIndex = icolor 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi, there
Just have a quick question maybe is not a quick one.
Is there anyway that I can use the conditional format with VB macro? I tried to record a macro with conditional format. but after I finished record, I checked back to the macro code there is nothing.
Just wonderling if VB macro can doing conditional format or not, if is doable, how? Please help!! Thank you

Hi

Could anyone help me with a formula to use for CF.

I have a coaching timetable that I use to know what coaching I have for the week, who with, total hrs....

As merging cells played havoc with some of the formulas and CF I had in place I have used CF to give the look of merged cells and keep the coach timetable looking professional as I intend to use this with all the coaching team to help with their planning!!

I did this by (Performance Centre as example):

Rule 1: cell value = "PC" (yellow fill/red font)
Rule 2: Formula =AND(B3="PC",B2="PC") (yellow fill/yellow font)

I repeated the above for all other coaching types (different fill/font)

The problem I now have that for all individual sessions I need to add a player initial so I know who I am coaching on what day. It would take ages to do conditional formatting unique to that player and the player initial could differ week to week!!

I have got this far (Programme Players as example):

Rule 1: Formula =ISNUMBER(SEARCH("PRO",B3:G32))

This coloured all cells the same even though the initial were different. It is the second part I cannot get to give the merged look, as the above (rule 2) only works when you put the exact text in "" I did try =AND(B3="PRO **",B2="PRO") as when used in formulas the * reflects text but this does not seem to be the case in CF. Without the merged look the sheet is far too clustered!!

I have attached an example copy of where I am up to so my above ramblings make sense!!

Cheers

Col

I'm trying to find a macro to apply conditional formatting to a large number of cells, but not having any luck.

What I would like to do is when cell E96 has a value of a, cells E3:F95 are shaded in grey. Then when cell G96 has a value of a, cells G3:H96 are shaded in grey, and so on down to IU96 having a value of a and cells IU3:IV96 shaded in grey.

Is this possible?

Is it possible to use conditional formatting to format several cells based on the value in another? What I want to be able to do is format columns A-G based on the value of column H. I tried it on the first row, by selecting cells A1 through G1 and entered the formula =H1=145.0. All that did was hilight cell A1. I thought it should have hilighted the entire range. I'd like to find a way to do it for an entire sheet. Can anyone help?
Thanks...

Hey everyone,

I'm running Excel 2003 and I want to be able to apply conditional formatting to autoshapes. I have a map and I'm looking to have the autoshape pick up a number from a table (done this) and then for the autoshape to show a different colour depending on the value in the cell. The purpose is to show a heat map of regional sales data i.e. if the autoshape value is between 0-10 show the autoshape blue, if it is between 500-1000 show if the autoshape as red etc etc

Any ideas on how I can do this?

Many thanks in advance!

David

I have a cell value based on a calculation,which gives me any one of 7
values.The cell values a-"DEAD","DEAD & DEAD","DEAD & LIVE","LIVE &
DEAD","LIVE","LIVE & LIVE",BLANK CELL. I would like to apply conditional
format of that cell-"DEAD" word to red bold font 11 with a color shade,and
"LIVE" word to blue bold font11 with the same color shade.Since I can apply
only three conditions and a cell value can't accept two formats at a time,I
am unable to do the task.Can any one suggest me how to do this.

Hi all!

I wonder if it is possible to add conditional formatting features to
highlight certain data within a pivot-table. All I could do until now is to
highlight the data I want via the normal way. That is, adding conditional
fomatting conditions to a group of cells, but when I change the pivot layout,
I lose everything.

Thanks for your help!

Raphael


No luck finding an answer? You could always try Google.