Set Cell Color Based On CheckBox State

Jan 18, 2008

I want to write code for a Checkbox that when checked it fills a cell with a color, but if it is subsequently unchecked, the cell color disappears.

View 9 Replies


ADVERTISEMENT

Tally Cell Automatically Based On CheckBox State

Jan 18, 2008

In my spreadsheet the user has the option to put in a numerical value into different cells, which will update (add to) another. So if we have an initail value of 20 in "I10" and the user puts in 20 in "W12", then I10 will display 30.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("W12:X24")) Is Nothing Then
Select Case Target
Case Is = Range("W12")
Logbook.Show
Range("I10").Value = CInt(Range("I10").Value) + CInt(Target.Value)
Case Is = Range("X12")
Logbook.Show
Range("J10").Value = CInt(Range("J10").Value) + CInt(Target.Value)
Case Is = Range("W13")
Logbook.Show.................

View 6 Replies View Related

Automatically Calculate Based On CheckBox State

Jan 23, 2008

I have a Worksheet_Change event like the one below. When Checkbox1 is ticked the it subtracts and when it's not, it adds:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim intValue As Integer
If Not Intersect(Target, Range("W12:X24")) Is Nothing Then
intValue = CInt(Target.Value)
If CheckBox1.Value Then intValue = intValue * -1
Select Case Target
' Apples
Case Is = Range("W12")
Logbook.Show
Range("I10").Value = CInt(Range("I10").Value) + intValue
Case Is = Range("X12")........................

View 7 Replies View Related

Call Macro Based On CheckBox State

Mar 12, 2008

I have a checkbox and a command button on my sheet. I want that, depending if the checkbox is marked or not, I can call two different macros. I don't know if this is possible at all or maybe can be done easier otheriwise, but this would more or less be my idea of what should happen:

Private Sub CommandButton1_Click ()
' If CheckBox1 = marked
Call MacroA

'Else (CheckBox1 = unmarked)
Call MacroB
' End If
End Sub

View 4 Replies View Related

Cell Color Change By State Of Other Feild

Jul 29, 2009

I'm playing with an excel file that has 3 different spreadsheets and basically the same layout just different product lines on each sheet. I need to find a snipet of VB code to change cell color depending on if another cell is used(filled in) or if it is blank. I'm very limited to my VB skills but can tinker enough to get what I need if someone points me in the right direction.

here is an example of what I'm trying to accomplish using the following Columns:

ItemNo[A], QUOTEREQUESTDATE[b], SUBMITTOSALESDATE[C] APPROVALRECEIVEDDATE[D], RELEASETOMFGDATE[E]

EXPECTED RESULTS TO CHANGE CELL[A] COLOR TO RED CELL:
[b] USED, [C] BLANK, [D] BLANK, [E] BLANK

EXPECTED RESULTS TO CHANGE CELL[A] COLOR TO ORANGE CELL:
[b] USED, [C] USED, [D] BLANK, [E] BLANK

EXPECTED RESULTS TO CHANGE CELL[A] COLOR TO YELLOW CELL:
[b] USED, [C] USED, [D] USED, [E] BLANK

EXPECTED RESULTS TO CHANGE CELL[A] COLOR TO GREEN CELL:
[b] USED, [C] USED, [D] USED, [E] USED

View 13 Replies View Related

Get State Of Checkbox On Worksheet

Jan 14, 2009

I need to get the state (xlon or xloff) of check boxes on a worksheet. i have tried the following but unable to get it to work.

View 3 Replies View Related

Default State For UserForm CheckBox Controls

Jun 14, 2008

I am having a little bit of difficulty find ways to effectively incorporate a checkbox panel into a form interface. What I am trying to do is all the user to select using checkboxes which of 31 separate columns will automatically be deleted or not deleted. The method I have been using is to create 31 checkboxes, and then use the value of the checkbox (true/false) to determine a value in an array, which then can be used to delete the column by a separate sub.

However, I am interested in being able to have the checkboxes start checked or start unchecked by values contained in a different array. My problem is that I am unable to reference the checkbox itself until the form containing the checkbox is opened, but at that point I don't know of a way to cause the desired checkboxes to automatically be selected without placing a command button that must be pressed to fill them in. Essentially I want to be able to open a userform that has checkboxes preselected.

View 5 Replies View Related

Returning State Cities By Inputting Given State Abbreviation

Jun 3, 2012

I'm trying to create an array formula that returns all the cities in a given state by imputing the state's abb in cell K2. The returned cities go into column J. California has the most cities(430) and the cities are 5000 rows deep.

View 7 Replies View Related

Conditional Formatting - Cell Color Based On Range Of Cell Color

Aug 9, 2013

Summary of performance of various products against target is as follows,

Product vs Target
Color Code
Result

CH4OH
Green
1.0

[Code] ........

I need the final result automated as follows,

If 2 green of the 4 products, then final result Gree
If 2 Amber of the 4 products, then final result amber
If 2 Red of the 4 products, final result Red

Is there a way to automate this?

View 8 Replies View Related

Color Cell Fonts Based On Text Color Of TextBox Controls On UserForm

Apr 11, 2008

I have got a userform with lots of controls,

One of the action's on a large group of the controls is the same but except for one number

here is an example

If TextBox107.ForeColor = 255 Then ActiveCell. Offset(0, 53).Font.ColorIndex = 3
If TextBox108.ForeColor = 255 Then ActiveCell.Offset(0, 54).Font.ColorIndex = 3
If TextBox109.ForeColor = 255 Then ActiveCell.Offset(0, 55).Font.ColorIndex = 3

This makes a cell that correlates to the textbox red if the text in the textbox is red.

Now, I loads of these textboxes that all need to run the same code with just the Offset value one digit higher than the last and I was hoping I could create a loop to avoid a huge block of code but I can't work out how to make a constant that will +1 with each loop.

Also, can I assume that a loop will start with the control with the lowest number i.e. Textbox1 and then work its way through the rest of them in order?

View 3 Replies View Related

Setting Color Of Range Based On Adjacent Cell Color

Nov 1, 2009

This is probably elementary, but I'm struggling and would appreciate any help as I have very little excel VBA experience to draw from.

I have assembled code which changes the cell color based on a value change in Column A. Column A will contain many different groups of repeating values. This code works well and and I have been able to figure out how to limit the number of colors to only 2. The end result is each set of similar values in column A is visually grouped by one of two alternating colors.

The number rows in the data set is variable as the data set is extracted from SAP. The number of columns is fixed.

What I want to do now is set the cell color in columns B through F the same color that was assigned to the row in column A. So if cell A3 is set to colorindex = 6, then I want to set the range of cells B3 to E3 to the same color.

Here is the code I am using to set the color of the cells in Column A:

View 7 Replies View Related

Change Font Color Based On Adjacent Cell Color

Apr 18, 2008

I have two columns. The first one (A) contains cells that have different Fill colors. The second column (B) contains text adjacent to the colored cells. I am trying to change the color of the text in the second column (B) to the corresponding color in the adjacent cell in the first column (A). I don't think conditional formating works well in this situation. I believe the solution would be some sort of macro.

View 3 Replies View Related

Change Text Color Based On Cell Color

Oct 17, 2006

I have various row cells in column (F) filled with the color Green. And corresponding text in Column G. How can I change the text of that particular row to white.

i.e.: if any cell in column F is Green, change the text color of that row in Column G to white?

View 5 Replies View Related

Color Cells Based On Color Of Preceeding Cell

Nov 18, 2007

I am looking for a very simple script that will achieve the following:

On the clicking of a button, Select and shade in a cell yellow, delete the yellow shading of the previous cell. The shading & selection should move up a column of cells, 1 at a time, in the following order:

From B10 to B9, then B9 to B8, B8 to B7 etc until the selection and shading is at B2. Once it is at B2 subsequent clicks will simply keep it at B2 (the top). Thus after 8 clicks the shading & selection should travel from B10 to B2, with only 1 cell being shaded yellow and selected at any one time.

View 5 Replies View Related

Hiding CheckBox Based On Cell Value

Mar 20, 2012

Here is what I have so far (debugger highlights my CheckBoxLD.Visable arguments)

Yes, this is an activeX checkbox

Code:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If Worksheets("Dosing").Range("BM21").Value = 0 Then
CheckBoxLD.visable = False
Else: CheckBoxLD.visable = True
End If
End Sub

View 4 Replies View Related

Write Text To Cell Based On Checkbox?

Jan 23, 2010

I have a userform with a checkbox. If the box is checked, I would likke to write "Option One" to cell A1 in Sheet1 when the Close button is clicked. I can't get the code correct for this. The workbook is attached.

View 2 Replies View Related

Checkbox To Change Cell Value Based On Other Checkboxes

Jun 4, 2008

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.

Private Sub 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.............................

View 6 Replies View Related

Return Value To Cell Based On CheckBox TRUE/FALSE

Mar 12, 2008

I would like to create a qestionairre with checkboxes used to answer the question "Do you need____" (Check if yes). On the following workbook I would like a list of all items needed, and a list of items not needed on yet another workbook.

Is there a way to create these lists without having blanks for values that are not true? (and because Im sure there is HOW?)

View 9 Replies View Related

Insert A Value Into The Active Cell In This Workbook Based On The Value Of A Checkbox In The Other Spreadsheet

Jul 25, 2007

I have 2 workbooks open, one called "Temp" and the other called "CRM 2"
My active workbook is "Temp". I am trying to insert a value into the active cell in this workbook, based upon the value of a checkbox in the other spreadsheet. Here's my

If Windows("CRM 2.xls").SelectedSheets.CheckBox6.Value = True Then Selection.Value = "By Phone"
I've tried variations on this such as:


If Windows("CRM 2.xls").ActiveSheet.CheckBox6.Value = True Then Selection.Value = "By Phone"

View 8 Replies View Related

Font Color Of Checkbox Text

Sep 18, 2009

I'm trying to have the font color of a checkbox text change according to what's in a certain cell. For example, if cell "A1" = "Boom" then the font should be red. This is what I've written:

View 3 Replies View Related

Return State Value Based On Match Of Name And Number Between High And Low

Aug 27, 2013

I have a set of data (Data tab) that lists name, a high number, a low number, and the state that is associated with the name and range between the high and low number (see attachment). I am trying to figure out a formula that will return the state value based on a match of the name and the number between the high and low. I am trying to use Index/Match but i cant figure out the "between the high and low" portion.

tester.xlsx

View 2 Replies View Related

Inserting State Names Based On Telephone Numbers

Oct 22, 2007

I have a large data set (excel file), of "Names", "Phone Numbers", and i need to sort this based on the States that correspond with the Phone numbers. The states currently do not exist in the spreadsheet, so my current problem is trying to insert those states into the spreadsheet.

There are over 100 area codes in the data set, so i'll likely have to write a large "If" statement in VB to run through them all, but that shouldnt be a problem.

NAME, PHONE, STATE
Bleh, 555-555-5555, =ChkState(B2)

I've been playing around with the VB Stuff in Excel and this is what i've come up with for trying to insert the State field

Function ChkState(pVal As String) As Long

Dim AreaCode As String
Dim StateAbrv As String

AreaCode = Left(pVal, 3)

If AreaCode = "201" Then
StateAbrv = "Test201"
ElseIf AreaCode = "203" Then
StateAbrv = "Test203"
ElseIf AreaCode = "555" Then
StateAbrv = "Test555"
Else
StateAbrv = "0"
End If
MsgBox StateAbrv
End Function

I'm fairly new to this VB stuff, my main problem stems from trying to insert the "StateAbrv" back into the Cell for the spreadsheet.

View 9 Replies View Related

Checkbox Vba: The Amount From The Subtotal Cell Will Auto Go To The Cell Next To The Checkbox

May 9, 2006

I have been using different keywords to find what I need. I have 3 checkboxes: cash, amex, other. I have a subtotal cell. When the appropriate checkbox is checked, the amount from the subtotal cell will auto go to the cell next to the checkbox. Is this VB?

View 6 Replies View Related

Change Chart Axis Font Color With A Checkbox

Aug 22, 2014

Any way to change the font color of a chart axis when a checkbox is set to true.

let checkbox be checkbox5 and chart be chart5. What I would like to happen is that if checkbox5 is true then the font color of the horizontal axis changed to grey (RGB 166,166,166), if it is false then I would like the font color of the horizontal axis to be black (RGB 0,0,0). I also would like this macro to be run every time the checkbox changes, or the corresponding cell changes to TRUE or FALSE (it is in another sheet, say sheet2). This is the code I have so far but all it is doing is showing the chart as selected and not changing the font color. I would also like it to not physically select the chart, this is for a dashboard so the selecting of the chart throws off some of the visuals a bit, but not sure how to do change the chart values without selecting it.

[Code] ......

View 7 Replies View Related

Change Form Checkbox Fill Color For ALL Checkboxes In Workbook When Checked (True)

Nov 20, 2013

I have a multi-worksheet workbook that has many forms control checkboxes throughout it. I'm looking for some VBA that will change the background (fill) color of ALL the checkbox when it is checked (True). I've seen code for a single checkbox, but not multiple/all boxes. I know just enough VBA to be dangerous, but I'm up to learning anything new.

View 3 Replies View Related

Excel 2010 :: VBA To Change Form Control Checkbox Background Color When Checked

Nov 22, 2013

I have an Excel 2010 workbook with many worksheets using hundreds of Form Control (not ActiveX) checkboxes. I need a bit of VBA to change Checkbox background color of each checkbox whenever the user checks the box. I assume this needs to be a click event? I don't even know the Checkbox property name I need to change I'm learning VBA as quick as I can

View 4 Replies View Related

IF Rule: Another Cell To State N/a

Jul 8, 2009

Depending on the text entered into one cell, I'd like another cell to state n/a. For example, if cell a2 shows the text of cash, then cell b2 would show n/a.

View 3 Replies View Related

Format Cell Via Check Box Value/State

Apr 15, 2008

format a cell depending on the state of a check(tick) box

View 3 Replies View Related

Excel 2010 :: Copy Data From One Cell Based On Color Of Another Cell To Different Worksheet

Jan 30, 2014

I have an Excel 2010 spreadsheet consisting of many worksheets (20 or so). Each of these worksheets contain detail level data regarding different projects. One of the columns in these worksheets is the 'Status' column (column F). There is conditional formatting on this column where if the text is 'G' then change background to a green color, 'Y'=yellow, 'R'=Red and 'U'=Grey.

The first worksheet is a summary sheet that I would like to pull information from each of the detail worksheet's columns B, D, E, G and H if the status column (Column F) is 'R' or 'Y'.

The number of rows in the detail worksheet can change each week (as few as 0 and as many as 100)

View 2 Replies View Related

Conditional Formatting - Change Cell Color Based On Date In Another Cell

Oct 9, 2013

I have two columns. In column B is the date of "last check". I column A is the date of "next check". I would like to have cell A2 in yellow color 334 days after the date entered in cell A3 and than in red color 365 days after the date entered in cell A3. Same thing for cell B2 related to date entered in cell B3. Yellow color in cells announces that check will expire within 30 days and red color that check has been expired.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved