My Request is in Two Parts.
Firstly, I would like to be able to List ALL the Colours Numbers in One Column & the Corresponding Colours Next to them Please in a Sheet Named "Summary" & Starting in Cell T3.
Secondly, I would like to Count How Many Cells in a Range on a Sheet Named "Network Points" and in Cells F4:Q179 has a Particular Colour Please. I would like the Totals to go on the Sheet Named "Summary" Starting in Cell W3.
I wish to count the "number" of numbers in a cell i.e.
Cell A1 has the following data: 12 25 25
This should return 3
Simillarly 4546546545646456 1 2
Should also return 3
I will have data such as the above in a row so i need the count to be able to count a range as opposed to the single cell example above.
Furthermore I need a separate count to count the number or red numbers in a cell. Same parameters as above.
I've got a function called countcolor (which I take no credit for) which counts a particular color in a range.
---------------------------------------------------------------------
Function CountColor(rColor As Range, rSumRange As Range)
Dim rCell As Range
Dim iCol As Integer
Dim vResult
iCol = rColor.Interior.ColorIndex
For Each rCell In rSumRange
If rCell.Interior.ColorIndex = iCol Then
vResult = vResult + 1
End If
Next rCell
CountColor = vResult
End Function
I have then set up a couple of macros which, when ran, fills a range of cells with a particular color. These colors are closely related but as you can see from the two macros below they ARE different. I'm finding my countcolor function is counting them twice though even though as you can see the color values are different.....
---------------------------------------------------------------------------
Sub pegcharged()
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 25600...........................
I'm sure this will be an easy query.
These are marks scored by 5 people in 10 exams
Name of the student
MARKS
MARKS
MARKS
MARKS
MARKS
MARKS
MARKS
MARKS
MARKS
MARKS[code]....
Range
A1 to K6
In each row top 5 MARKS should be filled with BLUE,VIOLET,YELLOW,RED AND PINK
If its not possible. Top 3 with dark colour, remaining 2 with light colour using conditional formatting
The idea came about after wanting to show a 'gold' 'silver' and 'bronze' background colour, but having to plump for yellow, grey and orange looked a little crap :f
If not, can a workaround be created by using code and a picture or 2?
Is there anyway to get a total but only using colours?
eg, I want to colour blocks in blue and at the end of the row, I want it to count how many were blue.
I want to know sum of cells according to their colours,but without using "macro".
to clarify my request, take a look in this attached file.
I am evaluating a wellness programme at work. One of the things we measure is BMI (Body Mass Index).
What I’m wanting to do is to categorise the BMI values into “flags”. For example someone who has a BMI of >35 could be red, someone with a BMI of 25-35 could be yellow, and <24 green.
What i’m wondering if excel can do is;
If I write the BMI value one cell can I get excel to automatically fill in another cell in either the colour or the name of the colour?
I would like to change the colour of a userform both the background colour and the font colour. However, in the colour property box I am met with &H00C0C0C0& (which is Grey). I can select some other colours but not the one I would like to use.
I have tried inserting RGB(194,214,154) however I get the error 'Invaild proprty value'
i am creating a spreadsheet to account for work being done on a team. At the moment the sheet adds up the average time that the whole team is taking to do a task,and it also adds up the time each person is taking.
so for instance: KL take 100 minutes to do the task. but the average time across the team is 90 minutes per task.
i have my list of staff with their times in column E. so what i want is if their time is higher than the average then the box turns red, if it is lower then green etc.
(the average figure is got by summing everyones totals in box B22.
I have a column where some cells are coloured yellow. In another column, I'd like to mark out these cells, e.g. if the cell next to it was yellow, I would like to to display 'Y'.
View 9 Replies View RelatedIs it possible to create a formula that looks at a cell (say A20) and if TRUE changes the colour of A22 to green if false to red?
View 9 Replies View RelatedI currently have the following macro which checks the cell contents and replaces a random number in a sheet (say Sheet1) with a name in another sheet (called 'Pool') making sure the names' font colour is the same. So for example in sheet1, cell A1 contains the number 1 - the macro looks at cell A1 in the 'Pool' sheet (in which cells A1 to A30 always contain the numbers 1 to 30) and replaces the number 1 in Sheet1 with the name in cell D1 including the same font colour. Basically, it is replacing numbers in one sheet with names on another.
As follows: ...
For a particular requirement I want to utilise the colours.
ANd therefore I need to know, how many colours are present in the Colour Palette in both 2003 and 2007 and which colours would be same if I make the File in 2007?
Are there 60 colours in 2003?
I have so many rows with numbers, when I do the matching I color them with different colors. I am looking for a color code adjacent cell with auto update.
View 9 Replies View RelatedAttached is a table detailing top 20 Incidents and Requests. Requests are highlighted in Yellow and Incidents are in Green. Associated with this table is a graph. What I like to do is if the bar on the graph represents a Request I want the colour to be yellow and if the bar represents an Incident I want it to be green. At the moment I can only choice one colour or a different colour for each bar.
View 4 Replies View RelatedI am in charge of a report spreadsheet which gets updated by my users multiple times a day. The spreadsheet is called the R-A-G report (red, amber, green). One of the columns has colours which are updated manually in accordance to the ammount of stock we have in the database (oracle column) and in our warehouse (utl column).
Right now the users update these colours manually, using a key at the bottom of the spreadsheet. My dream is to have these colours auto-updating when a macro is run.
I spent a little time and wrote a very basic macro, but there are a few problems with it. For one i do not think that the macro fills out of the possible variables; therefor it has around 50% accuracy.
I have uploaded the file and would really appreciate if i could get some feedback on how i could write this correctly, maybe some usefull websites specifically directed at elseif (if that is the correct code for me to use).
In a range, say A1:D5, I need to format cells as follows:
1) If cell contains only "H" or "HV": highlight red
2) If cell contains only a number: highlight blue
3) If cell contains a number with the letter "F" ("1 F" or "5.5 F"): highlight pink
i need a formula that does the following:
if the date is two months under the date it needs to be checked it goes yellow
if its over two months before it needs checked it goes green
if its passed the date it needs to be checked by it turns red
I have a vertical list where every other cell is coloured grey. The values contained in the list are regularly sorted into alphabetical order with new values being added and other being removed.
Is there a way to keep the cells alternate grey & white after I have sorted them? Currently, when I sort, if a cell is coloured grey and is moved down one cell the colour is moved and not just the values.
I have a conditional formula which populates a string depending on the value selected by user.
E.G of formula is:- =IF($B$1=0,"CONTACT PERSON - OPTIONAL",IF($B$1=5,"CONTACT PERSON - MANDATORY",""))
Where the result is "CONTACT PERSON - MANDATORY" then I want the word MANDATORY (not the whole string) to be displayed in RED.
I have a conditional formula which gives a value based on what the User specifies.
Example of formula is:
=IF($B$1=0,"CONTACT PERSON - OPTIONAL",IF($B$1=5,"CONTACT PERSON (FINANCE ONLY) - MANDATORY",""))
If the result of the string is " CONTACT PERSON (FINANCE ONLY) - MANDATORY " then
- word MANDATORY (not the whole string) should be in RED
- words (FINANCE ONLY) should be in BLUE
- remaining string should be left in BLANK
Not intending to use VBA. Is this possible?
Do any of the templates/backgrounds offer the user a white background with every fifth row and column an off-white colour?
My document will be read by LOADS of people, so I want to be able to follow my eye across. A soft system like that lets someone scroll across twenty columns and see they're still on the same row. It's like writing 7 Oct 2013 instead of 07/10/2013. It makes sense whether you use UK dd/mm/yyyy or US mm/dd/yyyy. People say 'October' not 'month no 10.' That's my logic and design principle.
I have two worksheets. both are almost identical although one has the colour red background on certain cells in the range.
How do i copy only the colours to the range on the other worksheet only?
Both ranges are identical and dynamic in size. The structure of both spreadsheets are identical.
how to give attractive colours to my ordinary macro buttons to which we assign macros
View 2 Replies View RelatedI have ammended the colours to 4 colours that I need for a risk assessment tool . I have added a second worksheet and put a dummy sample of my table there. Can you help me with how to put two colums of conditional formatting in the sheet? The risk rating before and that after the risk treatment? Many thanks in anticipation. I have highlighted in yellow the columns where the product of the consequence and likelihood is likely to be and this is where the colour coding needs to be applied. if you can help with putting the solution in the spread sheet that would be a great help. Any reply via this forum or via my email would be greatly appreciated.
I am new and I hope I have not made a mistake in starting a new thread. Sorry if I have but I wanted to bring this up as a new post so I get a sooner response.
We have only just converted to Windows 7 from Windows 2000 and can't find the option to reset default colours. In Windows 2000 it is in: tools/options/colour.
View 2 Replies View RelatedI have a very simple pie chart, no 3D angles, no shading, no tilting, no exploding (i.e. exploded view).
In fact it is so simple, it is just 2 sections.
I want one section to be GREEN, and the other section to be RED
Green is good, Red is bad, of course, just like traffic lights.
So, how do I get these colours to be green/red. Not worried about which data series (of 2) is a particular colour, I can swop these easily.
is it possible to create a pie chart with the colours that are in the cells (fill colour) which contains the data.
View 1 Replies View RelatedI normally create charts and use the same formatting for my colours i.e. Gradient fill with Shadow etc...
Now each time i create my charts i am having to keep do the formatting again and again and if i create a new spreadsheet with charts i have to do it all over again
Is there any way i can save this formatting colour so i can quickly apply to my chart series etc?