Free Microsoft Excel 2013 Quick Reference

Checkbox to change background color, font color and remove/ add bo

I am trying to make checkbox1 change font color, background color and
add/remove border lines if possible.

I am really not sure how to write the code for this at all. I would like to
change the font from black to yellow, and the background color in cell A3,
B3, C3 (one merged cell) from white to yellow. I would also like the black
border line around the cell to change as well, but I am not sure if that is
possible?

Thanks.


David:

I hate to post just to say thank you, but I've been looking for an answer to
this question for ages. I wish it was in the Excel help info.

Thanks for the help!

"David McRitchie" wrote:

> Cell Comments
> Changing Font etc in cell comments (#font)
> Excel uses the Tahoma font for new comment boxes, and you cannot change
> that default. You can set the default interior color, the font color, and the fontsize
> in the Control Panel (Display, Appearance, Tool Tip)"
> http://www.mvps.org/dmcritchie/excel/ccomment.htm#font
>
> You can run macros (also found in the above page) to change what you have.
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
> "TheN" > wrote in message ...
> > Can anybody tell me how to change the default font and size of a "Comment"?
> >
> > I can change the font and size of every comment separately but I can't find
> > a function to set a standard (default).
> >
> >
>
>
>

I have a macro creating background color. I created a message box that allows the user to change the color originally prescribed in the macro. I just do not know what code to insert after the message box so the user input changes the color of the macro.

Any references gladly accepted. I have searched extensively.

See attached workbook. - I am using Excel 2010 with Windows XP (32bit).

I am applying an autoformat to a pivot table and I want to know how to change
the colors, fonts, and bold to the autoformat. How do I accomplish that?

Thanks to David McRitchie for the event Macro below I am now able to change
background colors of cells as they are written. I now found that I have
another problem, is there a way to get the font to change colors, as in white
font with the
dark color of a cell and visa versa, a black font with lighter color cells?

thanks, Nick

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'David McRitchie, 2000-08-08 rev. 2000-08-14
' http://www.mvps.org/dmcritchie/excel/event.htm
Dim vLetter As String
Dim vColor As Integer
Dim cRange As Range
Dim cell As Range
'***************** check range ****
Set cRange = Intersect(Range("E2:E99"), Range(Target(1).Address))
If cRange Is Nothing Then Exit Sub
'**********************************

For Each cell In Target
vLetter = UCase(Left(cell.Value & " ", 1))
'see colors.htm and event.htm in same directory as
' http://www.mvps.org/dmcritchie/excel/excel.htm
vColor = 0 'default is no color
Select Case vLetter
Case "GF7"
vColor = 34
Case "GY9"
vColor = 36
Case "EV2"
vColor = 39
Case "EL5"
vColor = 41
Case "FJ6"
vColor = 38
Case "GY8"
vColor = 37
Case "FY1"
vColor = 35
Case "GA4"
vColor = 34
Case "FE5"
vColor = 36
Case "GB5"
vColor = 39
Case "GK6"
vColor = 41
Case "GB7"
vColor = 38
Case "GY4"
vColor = 37
Case "GE7"
vColor = 35
Case "GF3"
vColor = 39
Case "GT2"
vColor = 41
Case "GT8"
vColor = 38
Case "EW1"
vColor = 37
Case "TX9"
vColor = 35
End Select
Application.EnableEvents = False 'should be part of Change macro
cell.Interior.ColorIndex = vColor
Application.EnableEvents = True 'should be part of Change macro
Next cell
'Target.Offset(0, 1).Interior.colorindex = vColor
' use Text instead of Interior if you prefer
End Sub

Thanks to David McRitchie for the event Macro below I am now able to change
background colors of cells as they are written. I now found that I have
another problem, is there a way to get the font to change colors, as in white
font with the
dark color of a cell and visa versa, a black font with lighter color cells?

thanks, Nick

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'David McRitchie, 2000-08-08 rev. 2000-08-14
' http://www.mvps.org/dmcritchie/excel/event.htm
Dim vLetter As String
Dim vColor As Integer
Dim cRange As Range
Dim cell As Range
'***************** check range ****
Set cRange = Intersect(Range("E2:E99"), Range(Target(1).Address))
If cRange Is Nothing Then Exit Sub
'**********************************

For Each cell In Target
vLetter = UCase(Left(cell.Value & " ", 1))
'see colors.htm and event.htm in same directory as
' http://www.mvps.org/dmcritchie/excel/excel.htm
vColor = 0 'default is no color
Select Case vLetter
Case "GF7"
vColor = 34
Case "GY9"
vColor = 36
Case "EV2"
vColor = 39
Case "EL5"
vColor = 41
Case "FJ6"
vColor = 38
Case "GY8"
vColor = 37
Case "FY1"
vColor = 35
Case "GA4"
vColor = 34
Case "FE5"
vColor = 36
Case "GB5"
vColor = 39
Case "GK6"
vColor = 41
Case "GB7"
vColor = 38
Case "GY4"
vColor = 37
Case "GE7"
vColor = 35
Case "GF3"
vColor = 39
Case "GT2"
vColor = 41
Case "GT8"
vColor = 38
Case "EW1"
vColor = 37
Case "TX9"
vColor = 35
End Select
Application.EnableEvents = False 'should be part of Change macro
cell.Interior.ColorIndex = vColor
Application.EnableEvents = True 'should be part of Change macro
Next cell
'Target.Offset(0, 1).Interior.colorindex = vColor
' use Text instead of Interior if you prefer
End Sub

hi!
I use the "custom header&footer", and I want to to change the color of font, but I don't finde this choice!

How can I change a cell's background color for different words? Have a cell that says:

Status Codes: C=Complete, H=Hold, L=Late, OS=On Schedule

I'd like H=Hold to have a background of green and L=Late to have a background of yellow.

(Want to vary background color because font colors are being used for other meanings.)

Thanks!

Hi all !

I have a Plan with two columns, A and B. In both, I'm already using Conditional Formatting to change the color of the data in the cell, like this:

In the cells of column A, IF value are > 95, then change font color to red.
In the cells of column B, IF value are < 5, then change font color to red.

How can I change the background color of BOTH cells, if the two conditions above are reached ?

For example, if A12=96 AND B12=4.88, then background color of cells A12 and B12 must be yellow. It's important to keep the font color red, too.

Please, help me !!

Thanks a lot !!

Demian.

I would like to change the color of the cell based on the result of the formula. The formula is =IF(OR(G8>TODAY(),G8

My boss wants a spreadsheet that has multiple buttons on it that will change the background color of a specific cell to four different colors.

Example:

Text written in Cell B3, Button in Cell A3 that will change the Background color of Cell B3 to either Green, Yellow, Red or Blue.

There will be a lot of buttons on this worksheet following the same format as above. I don't want to change the value of what is in the cell, just the background color.

I would appreciate any help with this... Please.

Thank you in advance.

I need to use many different colors of tabs. The problem is that most of the
dark colors have thin white text. I fear that this will be difficult to read
for some people. I want to change the text to a wider/bolder one and the
color to something more visible.

I have to do a sheet and some of the cell have a red background. For some of
those i have to add some comments. I can not have the comments always on. The
fact the the background is the same color as the small triangle indicating
the presence of a comment (in the upper right corner) makes it invisible. I
have not been able the find the way to change the color of that indicator.
Please let me know how to do it.
Thnaks

Good morning all,
I am sorry to have question regarding Access 2000. I am desperate for help! So please forgive me!
When I right click, I don't see the fill color icon as I used to have before. I would like to change the background color for the control tab instead using the grey default color. Can I change default color of the control tab?
I would appreciate very much for your help!
Thanks in advanced!
Kim-Anh

I have to do a sheet and some of the cell have a red background. For some of
those i have to add some comments. I can not have the comments always on. The
fact the the background is the same color as the small triangle indicating
the presence of a comment (in the upper right corner) makes it invisible. I
have not been able the find the way to change the color of that indicator.
Please let me know how to do it.
Thnaks

I am using the following the code to change background color from yellow to white so I know when the field has been filled in:

Private Sub ComboBox1_Change()
 Select Case ComboBox1.Value
 Case "Select:"
 ComboBox1.BackColor = vbYellow
 Case "Yes"
 ComboBox1.BackColor = vbWhite
 Case "No"
 ComboBox1.BackColor = vbWhite
 Case ""
 ComboBox1.BackColor = vbYellow
 End Select
End Sub

I am going to be building larger lists and want to avoid having to put the case scenario in for each one and cannot figure out how to code this so that if the dropdown is blank or shows "Select:" the background should be yellow. If anything else is selected then background is white.

Is there a way to set the Case as the list name range??

Hey Guys

Today i thought "Hey I should try to give something back to you guys". So i start scrolling down the new posts and i come across "Run at Open" and i think to myself "This just got posted and I totally know how to do this". But someone had already gotten to it so i'll have to wait my turn to help someone

Until then i have a syntax question. I want to change the background color of one of the entires in my combobox listfillrange. If possible i do not want to modify the cell in which the listfillrange data is stored.

Combobox1 pull down menu
1
2<------modify this background color.
3
4

I know how to change the color of a regular cell using
ActiveCell.Interior.ColorIndex = 36
or
Range("A1:A6").Interior.Color = RGB(200, 160, 35)
but i do not know how to call a specific index in the listfillrange properly.

Please help
Thanks

Hi,

I use excel 2007 and have created a heat map on 3 color scale with red fill highlighting the areas of concern, but the text (black color) is not clearly visible in red fills. Is it possible to change the text color to 'white' using an excel function or VBA code? I tried to find the color of cells but have been unable to do so using VBA as the cells are conditionally formatted (due to heat map).
It is not possible for me to change the color coding of cells.

Thanks

hi everyone,
i am looking for a code which can be used to change the color of a series in a pivot chart based on it value.like if series value is greater than 60 - green,between 50 - 60,yellow and less than 50 red.
please help.

Hello,

I'm trying to find a way to use VBA to apply background color to my worksheet based on the value in Column A. The values start in cell A2. I would like that whole row to be light grey, then continue applying a light grey background until I reach a new value. Then no background until the value changes again, then back to light grey. It would alternate until it reaches a blank cell.

I attached an example. The tab named "P2 Expense Report (2)" is the one I need to color. The tab named "P2 Expense Report with Color" illustrates how I want my file to look after color is applied.

Thanks for your help!
Kat

I'm really just getting into VBA, so I hope you all can bear with me.

I currently have a spreadsheet with a userform that has multiple checkboxes. I want these checkboxes to change the value of cells depending on whether or not they are checked. 1 for not checked, 2 for checked. The column of the named range the cells are in is constant for each checkbox (though it is different for each checkbox and some checkboxes will need to change the value of a cell in multiple columns). The row changes depending on the value of a combobox on the userform. I have sorted out a code that works, but it seems very long, slow, and inefficient. I'm hopping someone can show me a better way to do it.

Here is the code:

	VB:
	
 CmbFinish_Click() 
     
    Dim rClSkills As Range 
    Dim rClLookup As Range 
    Dim var1 As Integer 
    Dim iDecision As Integer 
     
    Set rClSkills = Range("TblClSkills") 
    Set rClLookup = Range("ClassLookup") 
    iDecision = MsgBox("Are you sure you wish to change Skills?", vbYesNo, "Continue?") 
     
    If iDecision = vbYes Then 
         
         
        var1 = Application.WorksheetFunction.Match(cboClass.Value, rClLookup, 0) 
         
        If CboAppraise.Value = True Then 
            Application.WorksheetFunction.Index(rClSkills, var1, 1) = 2 
        Else 
            Application.WorksheetFunction.Index(rClSkills, var1, 1) = 1 
        End If 
    End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I've repeated this code (with changes to the column referenced in the index function) for each checkbox. The macro runs when you click on a command button located in the userform. It seems incredibly unwieldy. I hope you folks can help me clean this up. Thanks so much in advance.

Hi,

I am trying to use the background color of an image (without an image loaded) to indicate the status of a variable.

E.g Green for Yes, Red for No.

I am trying to write some code to do this.

Form_Name.Object_Name.BackColor.RGB = RGB(0, 255, 0)

This does not work as I get the error message: Invalid Qualifier.

I realise this is perhaps an odd use of an image so wondered if there is a way to do this or another more appropriate method I can use instead of a blank image to achieve the same effect?

Thanks

Andy

I need a code to change fill color of each cell in a range depending on it's value wich is pulled from a list.

I currently have it set up to change the color depending on a set value.

here is my current code (works fine, but I need to change the way it works)

Private Sub Worksheet_change(ByVal Target As Range)
Dim icolor As Integer
Dim oneCell As Range
If Not Intersect(Target, Range("Week")) Is Nothing Then
For Each oneCell In Application.Intersect(Target, Range("Week"))
Select Case oneCell.Value
Case ""
icolor = 0
Case "HERE"
icolor = 4
Case "VACATION"
icolor = 4
Case "BUSINESS TRIP"
icolor = 4
Case "OTHER"
icolor = 27
Case Else
icolor = 0
End Select
oneCell.Interior.ColorIndex = icolor
Next oneCell
End If
End Sub

There are more options on my list, but that is the basic code.

I plan on setting up a master list to pull options from, and sub catagory lists to determine the color of the cells.

Anyone help me?

Thanks

Does anyone know how to change the color of the row & column headers of the active cell?

For example, when you're in cell M25, both the column header "M" and the row header "25" change colors from grey to a greyish blue, which doesn't contrast very well with the grey of adjacent headers. This makes difficult to visualize in which column/row you are.

Any ideas on how to change this color to, say, a more contrasting orange or yellow? (like in the new Excel2007)

Thanks,

Alejandro Ramirez

I have some reporting Excel files that color numerical cells against
established criteria. If I have comments in the cell, and the cell becomes
red, I need the comment indicator to turn white or allow me to change the
color to one that is not used for evaluations.