We use a several Excel files to keep track of who has booked retail space with us. We have a main sheet that has every space within every shopping Mall we deal with. We then have different files for each Mall (this is so we can email the file to the shopping mall to show who is booked in each space but they don't see all the other shopping malls details). We have the other files set-up to read information from the main sheet so when we update a cell with the booked companies name it updates a cell on the relevant file.
This part all works fine, but we also change the booked companies colour depending on if they have they have paid, not paid or awaiting invoice. Currently we change this by hand on the main file then on the other files. I am hoping someone maybe able to explain a way of adding to the formula we already use to update the relevant cells automatically to enable the font (or cell if its easier) colour change to be carried across to the other files.
I'm new to VBA. I'm trying to write a macro that toggles between three or more font colors for the cells that are selected. I started with the "record macro" feature and then tinkered with the code because I obviously don't have a very good understanding of this process yet. How would I go about writing a code like this?
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.
I am a new member, with not a lot of VBA experience. Sorry for the vague title .. couldn't come up with something ^^". The idea is, I made an list with several anime titles I am watching (in column A) In column G it shows the status; "Watching", "Completed", "Planning to Watch", "On-Hold" or "Dropped". I managed to let the colors of those words change with this
Is there any way to have more colors in conditional formatting since it only allow maximum 3 colors. Anyway for your info I have an attendance sheet which I want the data to change colors every time the data is enter as below :-
MC / EL / NS = RED Y = BLUE AL = GREEN OD / RD = YELLOW PH = BLACK
I've worked out the following to change the tab color of a "sub" sheet when data on a master sheet is changed. Could anyone recommend the best way to apply this to multiple sheets (20+) without writing an "if" statement for each "sub" sheet?
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Worksheets("Sheet2").Range("E7").Value <> 0 Then Worksheets("Sheet2").Tab.ColorIndex = 4 Else: Worksheets("Sheet2").Tab.ColorIndex = xlColorIndexNone End If End Sub
The master sheet is a bill summary where totals are added, if one of the line items is a subcontracted item the total on that particular subs sheet changes from "$0" and i would like the tab color to change on any sub sheet that is affected by the current quantities billed.
In conditional format I've this formula =countif($Q$2:$V$2,B2)>0 which will return any matching numbers in yellow. My question is can I insert that once that row reaches 10 matching numbers the row turns green.
I want to conditionally format the fill colour of pie or donut segments based on either the value of the segment (e.g. red for negative, green for positive), or based on the value of related cells (e.g. Equal sized segments coloured red for related %age value >40, amber for 40 to 60, green >60).
I’m sure conditional formatting is the way forward with this but can’t quite get the formula right. I need 2 columns: Column A will have dates that an event happened on. Column B will be blank until we have received back in a letter that went out on the date in column A. At which point the date the letter was received back in will be entered into column B
What I need is for the Colour of Column B to change from green (before and on the date of Column A) to Amber (for the next 3 days) and then red (from Day 5 onwards. This would be until a date was entered in column B (the date the letter is received back in) at which point it would just go back to white. This would keep a check on how long the letter was out for and whether we need to chase it up or not and with an increasing urgency.
If a cells value is between to numbers then I need to change the color of that specific cell to green otherwise cell is to turn red. Also all cells without a value defaults to yellow.
I have to create a chart XY Scatter plot in excel. I have to differentiate the data points color based on the another column "category". Now I can change the data points color manually. Since the number of points is huge, is it possible to color the data points based on a column?
How to sort out a way to count cells whose fill colors have been set via conditional formatting.
I'm sure that I was once able to use Chip Pearson's CountOfCF function back in the days of Excel 2003, as it worked around the 3 condition limit and used .ColorIndex which assumed that you'd only ever be formatting using the .ColorIndex values of 1 to 56, but I can't seem to be able to get it to work in 2010.
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...
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 am looking to create a VBA that looks down a certain column and changes the font to red or green depending on the text in the cells in the column. If text = p then Green, If text = q then Red.This is the script I have (red only) how to incorporate the above criteria.Also the script in its current format comes up with an error.
Code: Sub ConditionalFormat() Sheets("Sheet1").Activate Worksheets("Sheet1").Select
I have four conditions that dictate font colors in column "d" of "sheet1" and am using the following Private Sub Font_Change(ByVal Target As Range) Set Myrange = Range("D2:D1000") For Each Cell In Myrange
If Cell.Value = "Started" Then Cell.Interior.ColorIndex = 3 End If If Cell.Value = "Pending" Then Cell.Interior.ColorIndex = 4 End If If Cell.Value = "On-going" Then Cell.Interior.ColorIndex = 18 End If If Cell.Value = "Completed" Then Cell.Interior.ColorIndex = 6 End If
Next End Sub
1. Does this code look valid? 2. Do I paste the code in a "module" or in a worksheet object? If I add this to a module, how does the code know to reference sheet1? 3. Is there a handy reference guide that shows color codes? If so, where can I get a copy.
I found in the web-site a great VBA code that replaces the "Conditional Formatting" option in Excel. The problem is that it changes the "fill color" and I also want to change the "font color".
Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer
If Not Intersect(Target, Range("C4:C14")) Is Nothing Then Select Case Target Case 0 icolor = 2 Case 1 icolor = 4 Case 2 icolor = 39 Case 3 icolor = 45 Case 4 icolor = 37 Case 5 icolor = 15 Case Else 'Whatever End Select
Using conditional formatting I can change the font color (to blue) of a cell if it meets a certain criteria. Now what I would like is to display a message when the color changes to blue. The cell range is E26 to E40.
I work with some stock index data and I would like to get rid of some dots and empty spaces etc. I used conditional formatting and changed the font color of all the values I need. When I try to sort them based on font color (in order to delete the empty spaces/dots) Excel does not do it. "Go to...conditional formatting" also doesn't work.
I have backwards co-workers that can't figure out how/why to use conditional formatting and have deigned it "not acceptable".
They've requested I fill in a HUGE spreadsheet and change the font color to red if it meets a criteria.
Simple enough to do with a conditional format that compares my data.
HOWEVER, now I need the individual cells to stay red and remove the conditional format (because conditional formatting "confuses" them). Removing the condition causes the color to go back to black. I've tried all sorts of paste specials (formula/format) to no avail...
I have a simple sheet that has columns I-K. My issue is that in column K I am dividing J/I. If both J and I cells have a "0" then I get the #DIV0 error. If both J and I cells are blank then I get the #VALUE! error. I need it so that if the cells in J and I have a zero in them then it places a 0 in K. But if both J and I are blank then it puts something else that I can have Conditional formatting change the font color so column K looks blank. I think the IF formulas I am try to use recognize blank cells as zeros as well.
Is it possible to write VBA code that will prevent a user from changing a cell's contents, depending on the cell's font color?
More specifically, I have a column of text in range B15:B64. Some of the cells will have a black font, others will be blue. Is it possible to lock the black font cells only, leaving the blue cells unlocked for users to change?
Cell B157 is changed by checkbox 1= TRUE, 0 = FALSE Cell H1 and Cell H2 are percentages changed by the user.
Range(AI8:AQ8,AI12:AQ12,AI16:AQ16,AI20:AQ20, etc) each cell in the range contains a formula, and is protected from the user.
I need the contents of each cell in the range to change font color (independent of each other) when the formula of any of the cells within the range changes - to the following
If B157 = 1
Cells < H1 (a percentage changed by user), would turn Red Cells > H1 but < H2 (a percentage changed by user) would turn Yellow Cells > H2 would be Green
If B157 = 0
Cells < H1 (a percentage changed by user), would turn Green Cells > H1 but < H2 (a percentage changed by user) would turn Yellow Cells > H2 would be Red
I need it to fire whenever the value of one of the cells in the range changes.
I have tried If statements, Select Case, and a combination of both but just can't get it!
I am trying to set up conditional formatting in cell C4 to change the font to a smaller number based on the value of C4. If C4 = 0 then Font Calibri 16, otherwise Font Calibri 24. However, the font size is not availabe, it is greyed out. Is there another way to accomplish this without using VBA? In the worksheet could I use an if() statement to change the font size?
