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

Free Microsoft Excel 2013 Quick Reference

How can I change sheet tab color based on cell value in sheet?

I use an Excel spreadsheet form to track various landscaping projects. I
denote the status of the project by using colors assigned by conditional
formatting based on the value of a status drop-down listbox on the sheet,
which works great, but I'd also like the tab color of the sheet to match the
status the various status colors I've chosen. Is there a way to do this?


Post your answer or comment

comments powered by Disqus
I am tring to get the font color for an entire row to change based on the
value of a cell in the row. For example, B1 = TRUE B2= FALSE and B3 = FALSE.
I would like the font color for all of row 1 to be red based on the fact that
B1 equals TRUE and the font color of the other two rows to remain black based
on the fact the the value in their respective B columns does not equal TRUE.

I tried conditional formatting but can only get the font in cell B1 to turn
red not all of row 1.

Hi! I really need help with changing a worksheet tab color based on a specific variable inside my worksheet. I have attached the workbook. In this workbook, There is a summary page for all open and closed projects. Between these 2 sheets are individual project sheet tabs for each individual project. i would like to be able to automatically change the tab color (light brown color) for each individual project based on row 3, column C , when "Closed" is chosen from the drop down list. I have a "project template" that is used for new projects, but would like this function to be in the already existing projects (263344, 261089, 11-422, etc....).

Any help would be greatly appreciated!
Thank you!
Stacy

Hi,
I am using the VBA. I want to change the line color based on the value
selected in the combo box. Can anybody help on this.

Thanks,
Avinash Sharma

Hi Guys,

I've been looking around for the answer and found partial answer here:

Forum > Microsoft Office Application Help - Excel Help forum > Excel Programming > Change Pivot Table Filter Based on Cell Value.

The answer posted by T-J was:

Sub Apply_Date_Filter_From_Worksheet()
    Dim pvtTable As PivotTable
    Dim pvtField As PivotField
    Dim pvtItem As PivotItem
    Dim filterDate As Date
    
    Set pvtTable = Worksheets("Pivot_Sheet").PivotTables("PivotTable1")
    Set pvtField = pvtTable.PivotFields("Date")

    filterDate = CDate(Worksheets("Controls_Sheet").Range("B2"))
    
    On Error Resume Next  'in case date not found
    
    
        For Each pvtItem In pvtField.PivotItems
            If CDate(pvtItem.Value) = filterDate Then
                pvtItem.Visible = True
            Else
                pvtItem.Visible = False
            End If
        Next pvtItem
       
    
    
End Sub
I can adapt this to my workbook & it works okay, but I need to change the filter to a number of different values in a list, some of which might not be available in the data.

Background :

I'm taking 14,000 entries of Incident data & pivoting the incidents based on where they were resolved, the filter will be based on which resolution group has been given a 'tag' of Onsite/Remote/Customer, these would each have their own tab creating the list i'd like to pivot from.

I need the pivot to display all incidents resolved by resolution groups 'tagged' as onsite (I.e. all those groups in list one on Sheet 'Onsite')

These may not all be present in the incident data, i dont know if that will cause errors if it attmempts to set the filter to something that isnt there...

Is it possible to change print ink color based on value in a cell for a
spreadsheet application? Example: If a cell's value is over 250, can I have
it print the number in red ink to draw my attention to it??

I am trying to automate the copy process of rows from one worksheet to
another based on specific data. I am running a DB query and returning the
data to my workbook. I then want to copy certain rows to another sheet based
on specific values in the first column. The data being returned is Date and
Time formatted as follows: mm/dd/yyyy 13:00 and is returned in ten minute
increments.
I want to automate copying rows from this worksheet to another worksheet
based on specific times in the first column. These values are not always in
the same rows.
Can I write a macro to copy the specific rows based on the time entered in
column one? If yes, what is the proper syntax?

I am trying to automate the copy process of rows from one worksheet to
another based on specific data. I am running a DB query and returning the
data to my workbook. I then want to copy certain rows to another sheet based
on specific values in the first column. The data being returned is Date and
Time formatted as follows: mm/dd/yyyy 13:00 and is returned in ten minute
increments.
I want to automate copying rows from this worksheet to another worksheet
based on specific times in the first column. These values are not always in
the same rows.
Can I write a macro to copy the specific rows based on the time entered in
column one? If yes, what is the proper syntax?

I am trying to use vba (excel 2007) to change the tab color depending on the value of a specific cell.

Example: Change tab color to red, if cell A1 = <2.5. Change tab color to green, if cell A1 = >2.5 but <4.

Any ideas?

Ryan

Is there a function that will change the font color based on a value in a
field?

in excel how can I get the number to show instead of #value in the cell where
I used a formula

Hello friends,

How do i generate number of rows based on cell value of column A.

column A column B
1 XG-01
1 XG-03
2 Yg-12
3 NX-11
1 LO-34
1 gx-44
4 fg-56
2 hj-04
3 mx-05
i want the output of column A like this

Column A Column B
1 XG-01
1 XG-03
2 Yg-12
2 Yg-12
3 NX-11
3 NX-11
3 NX-11
1 LO-34
1 gx-44
4 fg-56
4 fg-56
4 fg-56
4 fg-56
2 hj-04
2 hj-04
3 mx-05
3 mx-05
3 mx-05
Hope u guys would solve my problem , I am new to this forum and new to excel macros too....

thnaks

How can i change the color of a text in a cell automaticly if i write a text
in another cell.
Exampel: in cell C13 i have the text P2 in the color black. If ´the cell L25
is blank the text in cell C13 chould be black. If i type a text in cell L25
the text schould turn red.

2003 Excel. In workbook I have tabs colored green and I want to change the
color. When I right click on tab or go to format; tab color is not an option
I can select. Other people created the workbook. Could they have locked
something or is there some other reason why I can't change the tab color.

I use conditional formating in my workbooks and was wondering if there is a way to use the same concept to change the color of a tab based on the value in a cell.

Thanks!

I created a form for receptionists at a university to use in entering
registration information for students when they telephone.

I designed the form and protected it so that they could only access the
necessary cells. My question now is how do I change the tabbing order of the
cells? It goes from right to left, but I want it to very from top to bottom,
left to right.

Does that make sense? I want to be able to control the order of the fields
the form goes to when a user presses their "tab" key.

Please help!

Thanks,

Lynetta

Hello, I am trying to create a spreadsheet that will be used in a variety of different ways and I am looking for a way to have Excel rename various sheets based on a "preferences" sheet. For example, my sheet may have 6 sheets (Sheet1 thru Sheet6). In my preferences Sheet, I might have:
Column A Column B
Default Name Custom Name
Sheet1 Season
Sheet2 Mini_Plan
Sheet3 Group
Sheet4 Corp
Sheet5 Walk_up
Sheet6 Comp

In trying to make this more usable, I am trying to have the user fill out the custom names and have Excel automatically change the sheets names and now it can be easily used. I am pretty good with basic Excel functions but have not done anything with Macros or VBA.

Thanks in advance and I appreciate your insights!

I created a form for receptionists at a university to use in entering
registration information for students when they telephone.

I designed the form and protected it so that they could only access the
necessary cells. My question now is how do I change the tabbing order of the
cells? It goes from right to left, but I want it to very from top to bottom,
left to right.

Does that make sense? I want to be able to control the order of the fields
the form goes to when a user presses their "tab" key.

Please help!

Thanks,

Lynetta

How do I highlight a row based on the value in one of it's columns.
Lets says I have a list of tasks that I have to complete. One of the
columns is the status of the task. When I select the Complete status in that
column I want the whole row to automatically be grayed out. Is this
possible? How can I do this?

trying to figure out how to fill a row a color based on a certain value of a
call.

Morning all, hope you're all doing well. New member here

I'll dive straight to the point....

Recently started an Excel unit in an IT/Admin course and having previously sailed through the basic Excel formula and functions (stuff like :IF, SUM, Concatenate, conditional formatting, FV, Subtotals), I've got myself stuck trying to create a nifty tool using Macros in VBA.

I've produced an "Investment Table" using the FV function to allow the user to input things like interest rates, payments, number of payments, years of investment etc, and using these values the FV function throws out the resulting value (future value). Now, on a separate page I'm trying to "break down" this tool into numerous year-by-year tables showing how the value of the user's investment changes year-by-year and payment-by-payment.

For example, Mr Smith invests £100, 4 times per year, for 5 years, with an interest rate of 3.5%. Currently, all he can see is what his value will be worth at the end of the 5 year investment. But using the year-by-year tables he'd see a separate table for each year, and so he can see that in Year 2, Payment 3 his investment will be worth "X-value".

Here's where the macro comes in; in the main "Investment Table" the user inputs the "Years of Investment" (Cell D2). Using this (number) value, and on a separate sheet, a macro needs to produce/replicate the year-by-year table based on the value in cell D2. The aim of this is to save the user having to C&P the table or create their own, X-amount of times. The macro is needed because the tool must be flexible to account for different lengths of investment - and so obviously it's not practical to have to C&P if a user is investing for, say, 25 years.

Put simply, "Table X" must be copy & pasted Y amount of times, where Y = D2
I've tried to find somewhere to start in VBA, having done basic macros in it before, but I'm lost

Hope this makes sense but I will of course clarify if needed.

Thanks

Hi,

Is it possible to rename a sheet in a workbook based on the value in cell E2 (a number) concatenated with cell F2 (a date). The other problem is the date is in the format 11/07/2011 and i want it to be represented as 20110711.

I've tried concatenating the cells in the sheet but i lose the date formatting and it just does it as a number.

Basically in the end i want the sheet tab to say for eg.

33594037__20110711

Also it would be great if the workbook could be named the same.

Is this even possible??

Cheers, Stu

Hi,

I need to Color a particular column based on the value that is entered in the First column of that specific Row.

Eg: A1 contains 4, i need E1 to be colored
A2 contains 5, i need F2 to be colored.

Do i need to write a macro for this or can conditional formatting work ?

In my workbook sheet 11 has some ranges that need to have names based on cell values in sheet2 (for purposes of data validation lists).

Range S28:S46 will assume the name of sheet2A11 & sheet2A3.
(example name period_1unit_1)

Range U28:U46 will assume the name of sheet2A11 & sheet2A4.

Range W28:W46 will assume the name of sheet2A11 & sheet2A5

etc.

Right now I am calling the code when something is entered into A11.

I have tried if statement and select case, but I ran into complications with both.

I have posted both codes with the questions I have concerning those codes.

Can you help me either use one of these codes or come up with a better way?

SELECT CASE METHOD

Select Case
Target.Address

    Case "$A$ll"
        Sheet11.Range("S28:S46").Name = Target & Range("A3")
        Sheet11.Range("U28:U46").Name = Target & Range("A4")'
        Sheet11.Range("W28:W46").Name = Target & Range("A5")
        Sheet11.Range("Y28:Y46").Name = Target & Range("A6")
        Sheet11.Range("AA28:AA46").Name = Target & Range("A7")
    
    Case "$A$32"
        Sheet11.Range("S49:S54").Name = Range("A3") & Target
    
End Select
Nothing happens with this code, and I know it is because I do not have a value assigned to A11, but I have no idea what the user will enter into A11. I just need all those named ranges to occur once something is entered into A11.

IF STATEMENT METHOD

    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Address = "$A$11" Then Sheet11.Range("S28:S46").Name = Target &
Range("A3")
This code returns the desired result; however, I need to add to it, and I am not sure how. Something being entered into A11 results in 4 name codes. With an if statement I do not know how to display multiple results, which is why I tried the select case method

Hi,
Hi,

I want to lock a cell Range based on cell value in another ther cell.

For instance , The cell A1 contain a formula with Logical value True/False, and If A1 is True i want to Lock all the Rnage B1:AA1 and dont want to enter any values to this range. I used below code and the cell format protection is getting changed, but when i protect the sheet and change the Value in A1 its getting Error "Unable to set the locked property of the range class"... Can you please help.............

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("A1").Value = 10 Then
Range("B1:AA1").Locked = False
Else
Range("B1:AA1").Locked = True
End If
End Sub


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