We have a workbook with over 20 worksheets, each sheet with one or more tables in it. Several people use the workbook and we have decided that when someone changes a cell, they should mark the changed cell by filling it with blue using Format Cell. We have then nominated one person at end of each week who will check all the changes made during the week and either approve or reject them. So her task is to find all the blue cells, check them and then take the shading off.
What I would like to do is to create a macro to help her. I would like the macro to automatically identify all the blue cells in the workbook and give her a list of these cells. I wonder if someone could give me direction on a couple of things:
1) The command in the macro that would return whether a cell is shaded blue
2) The way in which I could cycle through all the content of each worksheet. I'm thinking worse case I would have to write instructions to go to each sheet, and then do an outer loop to go through each row, and an inner loop for each column (and the number of active cells varies by worksheet) -
Like u see in attachment i have products in cells wich are painted in different colors. I need to compare two numbers of different product, and write result to column "Type D,C,B". If one of two products is without color/white or they are not the same color then result is always "D", if both products/cells have same color and different number then results is "C", if the color and number is the same then result is "B"
Below cell B2 I will write numbers by hand, when I finish and start macro it will compare first cell B2 and B3 and write result to C3, then compare B3 and B4 and write result to C4 and do that until there is any number in column B:B.
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.
My Excel spreadsheet is linked to an Access database to Get external Data. When the data is periodically refreshed ,while the spreadsheet is open, I need to automatically detect it so that a macro can be run each time to format the new data. The Excel data is not a pivot table. The spreadsheet is for display of data only (no user updating).
In column A I have a list of Acronyms from A2:A90000 and more
In column B I have the corresponding acronyms spelt out from B2:B90000 and more
When I run the macro, it shoud detect the multiple/duplicate Acronyms and it's corresponding descriptions, DELETE the multiples/duplicates and move the cells up.
is it possible through vba code to determine the macro security level of Excel 2007 and inform the user. I tried the code below , it is executed when the workbook is opened, but it doesn't work.The code below is executed only when the macro security level is "Low". My goal is at least to inform , with a msgbox function, to change manually the security level.
Code:
Dim secAutomation As MsoAutomationSecurity Dim zLevel As String secAutomation = Application.AutomationSecurity Select Case secAutomation
I am trying to detect if a user leaves a cell empty. I have tried If statements like:
If Target.Value = "" Then
and
If (IsNull(Target.Value) Then
The problem is when the user uses the spacebar to delete an entry in that cell. If they leave any spaces after exiting the cell those if statements fail to run.
I've a sheet that presents the profitability of sales locations in a geographic layout (similar to Tables in a restaurant), each sales location is a cell, with roughly 1,600 locations presented. Each location is colour coded based on performance / measure, in a basic thermographic way [e.g. dark blue for very poor, solid red for high performance] with users being able to change metrics and re-colour cells accordingly.
The sheet is already information rich, but I'm wanting to detect the cell address directly under the pointer and populate a text box or other cell based on the value in the cell directly below the mouse pointer.
My question is; Is there a simple way of detecting the mouse position on the XL Grid? nb. Want to avoid having to activate cell beneath pointer.
I am try to do a test on cell to see if it’s a string, and then do something if it is: IF Range ("a1").value "equal a string" THEN. But I am not very experience in VBA
I'm doing the vba programming for excel where I added a button to the excel, and when user click on the button, it will trigger my function.
My question is: if currently there is a cell been focused, how can I know whether that is in the 'editing mode' (ie, focus on that cell and double click on that) or merely 'focus' on that cell? Any of the excel object property for this?
I need an equation that lets me determine whether a cell contains a static constant value (either a string or a number), or an equation that generates a value.
Problem: I have a cell that contains a default equation that generates a default value, depending on a few conditions. The user can overwrite this cell with a constant. In another cell I need to know whether that cell is still the default equation or a new constant value.
I cannot simply test the value of the contents to see if they match my default, because the user may choose to enter that value.
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'm working on a timetable for my boss and i'm trying figure out how to have vba detect when a specific set of cells gets an x typed into it and calculate 1 cell = 36.50 hours and input into the appropriate Sum column. The Sum Total is Column AR and i've put x's into some of the cells that will be containing them, i'm sure if i just get the basic code needed i will be able to implement it.
Is it possible to check the conditional state for a specific condition for a specific cell. For example a cell has 1 or more conditions.
A condition will toggle to a condition if true or false etc. The specifics is i use red (color 255) if a cell has not met a specific condition (of 1 or more conditions) So if the cell is mandatory to be populated, and is blank, it will be red, if not it will be something else.
But i want to loop all visible cells with conditional formatting, and where the cell has a condition when true will show interior color = to 255 return the cell address. To msg the user where data entry is still required etc.
I have some excel sheets that are formatted like the following:
COMPANY | TOTAL | R | G | B company1 | 10 | 255 | 000 | 000 company2 | 20 | 000 | 255 | 000 company3 | 30 | 000 | 000 | 255 ... and so on...
My question is that I would like to have a macro that runs on this basic file and creates a bar graph with the data. Then it utilizes the RGB values in the columns to change the specific bar for that row. So setting the r, g, b as variables corresponding to the columns in the sheet. Also there isn't a preset number of rows in the files.
I am working on a project where I am virtually almost finished except for a minor change with the pie chart. I am analyzing some data and recorded a macro to do this and also the pie chart for visualization. However, I do not like the color of the default pie chart colors and would like to customize it. How could I change this within the macro I have recorded?
I am in the process of reformatting an excel workbook to act as a review tool for different factors in a process. Part of my redesign includes the use of coding that creates different cell colors based upon the cell contents. The new workbooks will be used to handle existing data for this year. I have developed a process macro to open an existing workbook and copy and transfer the original data worksheet into the newly formatted workbook. The data gets transferred to the new worksheet and the resulting workbook is renamed and saved, Heres'' the rub... the newly saved workbook is missing all of the coding for the worksheets... apparently this is a MS bug.
Has anyone figured a workaround for this. One thought I had is to open both( new and old )workbooks and rather than move/copy , i would transfer the data using cell references.
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 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.
I have read the instructions for conditional formatting and I don't think that is what I need. I have 10 diff year 2005,06,07 etc. I want each cell with with a diff year to turn a diff color. Like I want 2005 to be red and 2008 to be blue.
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 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.
when I copy a worksheet from one workbook to another, the colors on the sheet in the destination workbook, become dull/faded. They actually change from the original colors to some off colors. I copy by selecting the source sheet, hold down ctrl key, then drag from one workbook to another). Also, is there a way to get the sheet back to the original colors.
How do I make the colors of certain cells on my spreadsheet static and unchangeable by cell data that may be copied and pasted from other cells of different color? Is this possible?
I have a few buttons on my page that I would like to change colors depending on the value in cell AI1. Right now this is the code I have:
[Code] .......
What I would like is to add formatting for "rounded rectangle 4" as well. if the cell = 1, number 4 changes color, if the cell = 2 both change color, and if the cell =3 then only 5 changes color.
