I have 2 cells (B2 and C2) with date format, showing dates from a calendar.
FYI, These cells are NOT in my Calendar.
Now, I need to write a formula for my calendar cells to check these two cells (B2 and C2) and change the color of the cells inside my calendar according to the date sindicated inthese two cells (B2 and C2).
I did this with numbers using conditional formating and it works perfect but doing this with dates is giving me a hard time. Is there a way to do this? below is a link to my excel file.
When a workbook is opened, (using OnOpen() ), I trying to get two header titles colors (A2, B2) to change from their usual Black to Red to Black to Red and finally back to Back.
I'm battling, especially with the time (1 second) for each change. I'm probably using the wrong approach. Can anyone suggest a sound method to achieve this?
Trying to format cells b9 through b 24 and cells c9 through c24 to change from white to a light grey color by clicking the mouse button once. so one click white, another click grey. The color grey I need is white, background 1, darker 25% in the autofill field in excel. I have search other forums they all lead to changing the code, I did this already. just copy pasted a code I found but all cells were selected to change a bright green.
I'm trying to have change if the date on it is passed "now()". I am already using the 3 conditional formatting fields and need this one and another one. Here are 2 problems I seem to be having.
First - the code below only works if I change the date on the cell. I want code to either work with Worksheet Active or any other way so the user does not have to redo the date’s everyday. Second - a record might have conditional formatting already. Is it possible for the target cell in this code can show this color while the rest of the row shows the color of the conditional formatting?
Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("G1:G2000")) Is Nothing Then Select Case Target Case Is <= Now() icolor = 39
I have a worksheet where I want the to change the collor of a range of cells in accordance to the value in another range of cells.
So if the value is "Gron" in cells C4:C27 then I want cells J4:J27 to return the color green. If, however, the value is "Rod" in cells C4:C27 then I want cells J4:J27 to return the color red.
i'd like to use a macro to loop through a range of cells and change the interior color index based on the cell values (ex. if cell value < 10, set interior to blue, if cell value > 400, set to green) i'm trying a for each loop with an if statement but i keep getting syntax errors, i know this is probably a simple problem but i'm not experienced in vb,
I have the following Case statement that changes all the cells to the correct color, but does not change the Font color as specified in the Case. All the text is White (fcolor = 2) instead of just the 3 types of cells specified.
so I know how to change the color of a command button(right click, properties), but is there a way to change the color to a specific red green blue range? lets say 102 28 02 for example
I have a large workbook, with multiple sheets. I would like to be able to select a name from a drop down menu, and have the cell color change for all entries of that name in my other sheets. I can do it manually, but it would be much cleaner and easier to have Excel do it for me. I have looked at other answers here on this forum and know that if it can be done,
I'm a average excel user, i know my way around it and kow common and basic formulee, VB isnt a strong point, but i am willing to attemp anything for this, let me explain
Lets say in colum A I have a list of items, in colum B i have a check box, currently the check box is red, if i tpye a x it turns green due to conditional formating. That works fine...onto the problem...
Lets say in colum A i have my items but they are repeated...eg A1, A145, A166, A122 are all the same item, what i want is regardless of where i place the x each check box under coloum B either gets a X and turns green, or just turns green, i dont mind...
I dont think conditional formatting can cut this, though i may be wrong. Ive tried to use the IF function under condtional formatting but ran into problems...
The company I work for does not use the usual calendar dates and uses a modified calendar. As an example, the month of January is Dec 31 thru Jan 27, February is Jan 28 thru Feb 24 and so on. I need to group data using a pivot table and summarize data by month, but as I just described above, calendar months will not work. Is there a way to modify what Excel sees as monthly dates?
My pallet lost color-coding - if I hover over each little scare it displays the names for the colors and if I click on them they color the cells with the right colors, but the palette itself lost the visual display of colors except for 8 colors: black, blue, red, magenta, yellow, cyan, and white.
I use color-coding of cells a lot and I find it difficult to work without visual clues. At least the hover-support allows me to get the work done, but with difficulty.
I need to change the color of a specific cell when conditions are met in a different cell and it seems like it may be beyond the scope of simply applying conditional formatting. What vba code would look like in proper syntax to make the following statement true. IF cell value Q4 is >=cell value S4 AND cell value R4 is <100 Then cell Q4 turns red and IF cell value Q4 is >= cell value S4 AND cell value R4 =100 then cell Q4 turns green. it may also be useful to know that the "cell values" in this example will be dates and not straight values. this may be possible to do using conditional formatting, but i cannot find a formula that works.
I have a program where I can update the calibration due date of an item. I have attached a cut down version of my program showing the relevant areas. There is usually password protection on the worksheet so it can only be edited via the form (the vba coding removes the password protection before editing, then re-enables the password protection after editing). The "Update Calibration" button is usually on a "Menu" sheet.
Once the form is opened a serial number is typed in the textbox. The calendar button is then clicked, which brings up another form with the calendar on. The due date is selected on the calendar. When "OK" is clicked, the date label caption is then changed to the selected calendar date. When "Submit" is clicked, the spreadsheet will search for the Serial Number, once found, the label caption (being the date selected) will be entered into the cell to the right of the serial.
If the day selected on the calendar is greater than 12 the date is entered correctly onto the sheet. example: calendar date selected = 15/01/2010. shown on sheet as 15/01/2010. However, if the day selected on the calendar is 12 or less, the date is for some reason entered incorrectly onto the sheet. example: calendar date selected = 08/12/2010. shown on sheet as 12/08/2010???? What is going on here? how come the day and month are swapped around if the day is less than 12????
I want to select a date of choice from a calendar in a field within an Excel spreadsheet.
I do not know how to do userforms and so that is why I wonder if I can add this in just a spreadsheet.
This is how far I have gotten so far: After doing the following, I have a static calendar setting in my spreadsheet like a text box showing the current date.
Developer Insert More Controls Calendar Control 12.0
I am using a great pop up calendar without ActiveX made by a guy called Phil Johnson. When you click a specified cell the calendar appears. The only problem is that the calendar doesn't default to today's date when it opens.
I have the original file Calendar source code if anyone needs it.
I currently have a worksheet that when a user clicks on the cell, a calendar form opens. From here they can select a date, click ok, and the date gets placed in the corresponding cell they clicked on.
Now if the user needs to change that date, i want them to be able to click on the cell again, and when the calendar opens, the date in the cell would be selected on the calendar. I was able to do this in a userform, but im not sure how to edit it to work on the worksheet. I am using the below to populate the calendar.
Code: private sub worksheet_selectionchange(byval target as range) if not intersect(target,range("aa16:aa24")) is nothing then .show calendar When the user clicks the date, the below code places it in the cell.
Code: private sub ok_click() with active cell .value=calendar1.value end with unload me end sub
Now the below code was used when I was using a userform, but im not sure how to edit this to pull the date from the active cell.
Code: privatesub userform_activate() me.calendar1=date if not tb is nothing then if isdate(tb.value)then me.calendar1.value = tb.value end if end sub
This may have been answered on here but can not seem to find it. My situation is I have values in A1,A2 & A3 that are like counter reading so the value is always changing. What I am looking to do is change the cell color if one of the values is over 500 from the other two values. Say A1 is 3000, A2 is 3250 and A3 is 3500. I would like the cell for A3 to change color.
Moving between different sheets changes the procedure window, which is a good thing, but can also can be error prone and annoying to search through several windows searching for the procedure or module currently working on. Is there a setting somewhere to prevent VBE from changing the current procedure window?
How to change the color of a single tabe. What I'm trying to do now is reset the tab color for all tabs.
Here is my code:
'REMOVE TAB COLOR Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets With ActiveWorkbook.ActiveSheet.Tab .ColorIndex = xlNone .TintAndShade = 0 End With On Error Resume Next Next ws
I'm new to using loops so I'm not exactly sure what the above code is doing. When I debug it goes through the steps without any errors but it doesn't actually change the tab colors.
The following code is the code "behind" the run button on my userform. The last section reads in a column ( of numbers and text) into a listbox based on the users choice. Is there any way I can have only certain rows in a different color - based on either if the value in that row is greater than a certain number of if the text in that row is a particular sentence?
Private Sub CommandButton1_Click() 'Dimension all variables Dim fqc As Long Dim eptg As Long Dim newlabel As String Dim BorC As String Dim now As Long Dim secondorthird As String
'Collect User Choices fqc = TextBox3.Value ' Final Quiz Choice eptg = TextBox5.Value ' Expected Post Test Grade BorC = TextBox4.Value ' Is desired grade a B or C now = TextBox6.Value ' Number Of Weeks after term has started secondorthird = "second" If (now = 3) Then secondorthird = "third" .....
For some reason colors will not show on any EXcel doc. When I type colored text in a cell, it shows the color until I press enter, then it goes black. Cell fill colors do not show at all.
However when I send the doc to another computer the colors wil show.
I am trying to create a Holiday/Absence Sheet that will count up hours of Holidays, absence, business Visits etc etc.
The only way I can think of doing this is by colour and filling the cells with the Number of hours holidays, Absence & Business Visits but to do this I need to create a formula that will sum by colour. I've tried a variety of things and I'm sure this can be done using VBA but it's something I have never used so I am at a loss.
So, what I need is a SUM (A1:A31 if colour= Red) and SUM (A1:A31 if colour= Blue) etc.
I hope this makes sense or one of you geniuses out there can come up with somethign smart that works better than my idea!
I have a big set of data and I want to create different graphs in different tabs with it.
So Imagine that in Tab 1 I want to do the sales forecast for the next five years for product A for all countries in Europe Tab 2 would be the same, but for product b Tab 3 the same, but for product c
So with dynamic tables in each tab I can easily do my graph
The problems is that since values vary per product country A in tab 1 is red, in tab 2 is blue and in tab 3 may not exist.
How can I set up a template so taht I do not have to color code each country in each tab? I.e. I want Spain to be blue in all my graphs in the workbook, Italy red and so on...
How do I go about formatting selected cells for different colors for each different number in the selected cells? If I have numbers scattered about I want the cell to show a different color depending on the number that is inserted. EG: 115=red, 365=blue.
I am making some grids which will have a series of numbers in the individual cells. But I can't figure out how to change the colors of the digits. If I change one digit to Red; all the numbers in that cell change to Red. Whereas I would like to have them as Red,Green,Black,Blue,etc.
