What I need to do is have a cell that will be say yellow until there is information put into this cell. The information could be in the format of text or numbers. The information would not always be the same so it would need to be yellow when there is no information in the cell and another colour or white when there is information in the cell.
View 6 RepliesI have the names of companies in one column, and the amount they owe in cells in the column beside them. I then have a second list of companies that is a subset of the first. Is there a formula that would place the amount they owe in teh corresponding cell adjacent to the compny in the second list? I've attached a sample workbook, Full Company List in column A, amount owing in B, trimmed down list in D and ideally I'd like the corresponding values in E.
View 3 Replies View RelatedI have a file consisting of two columns, called, "bom ref" and "material"
I need to show the data as indicated in the file, headed required format required.
In effect where I have a 0, that is the material I need to show for every row with a bom ref. of 1,2 or 3, down as far as the next 0 but not including the 0, when I reach the next 0, the material is a new letter, and that letter needs to repeat down as far as the next 0, but not including the 0 and so on.
on sheet1 cell a1 is there a way to make sheet1,s name appear as the contents of cell a1
View 9 Replies View RelatedI have a list of sales enquiries 1600 rows long. Each has a brand recorded in cell J (from j4 above is headers)
I have 4 brands
A, B, C & D
I would like to copy the data from the master sheet into 4 tabs lables A, B, C & D depending on the brand recorded in J
I have a template I have been asked to amend and I know I need to use Conditional Formatting I just can't crack the formula I need to use. Cell A2 will have either "CD" or "CW" or "IN" input into it
When user inputs either "CD" or "CW" into cell A2, I want cell G2 to have "Margin Movement" automatically displayed. When user inputs "IN" into cell A2, I want cell F2 to have "MV" automatically displayed, and I want cell G2 to be coloured in bright red fill. Then user is to go to cell G2 and input some text as a description, and I want the red fill to disappear once they have input something in there.
I thought I had it licked using IF statements for the "Margin Movement" and the "MV" cells, but I can't get the conditional formatting to work - I assume this is because the cells aren't technically "blank", as they have IF statements in them.
I'm trying to create a tag with a color border. What I desire is to fill the BLANK cells around the tag, A1:D1 + D1:D19 + A1:D19 + A1:19 in a certain color based on the text value of the cell B11. There are 5 different values, such that if the B11 read Red Sox - the boarder is going to be red, if it reads Houston Astros it will be dark blue, etc..
I have a similar problem with changing the color of the cell based on the month. So regardless of the year, 2014, 2015, 2016, etc... If I use MONTH() function I can just get numbers from 1-12. I want Cell C16-C18 to be certain color depending the date entered in cell C17 such that for each quarter, months 1-3, 4-6, 7-9, 10-12 they are different color.
I have had no luck with conditional formatting (and I also believe that it is good up to 3 cases only). I am decent in logic/programming language but have little knowledge with macro notation and especially how to run them in excel 2013. I do know how to start it alt+F11 and that I need to make sure that code is written under the specific sheet where my tag is located.
I have created two buttons in a worksheet to navigate to two other worksheets. I would like a cell to display 'YES' automatically if one of the two buttons is clicked and or to display 'NO'. I also would like to change the colors of these cells change automatically depending on which button is pressed.
View 2 Replies View RelatedI am quite a novice with excel and I am trying to get a cell to show blank when I select a drop down list value in another cell.
Cell D2 contains a validated list containing two items (Air, Vacuum).
If I select Vacuum I wish to change another cells (D4) contents to blank, D4 is also a validated list containing three items (Yes, No, BLANK) the blank is actually a blank space and not the text blank.
I can get the cell to operate correctly but if I select Air in Cell D2 and then Yes in cell D4, but then change my mind and reset to Vacuum in cell D2 i need the cell D4 to clear its contents automatically.
I inherited a worksheet with 70k+ rows of data. As shown below each row contains a record number(Col A) with the date(Col B) it was created and value(Col C). There are 5 records per day. You can see in the example below that Col A has the value 30 duplicated for each day. Is it possible to rename only the second "30" in Col A for each day?
Col A
Col B
Col C
10
1/14/2014
10
[Code] .........
I'm having a difficult time figuring out out to coordinate two cells together.
If a "1" or "2" is entered into cell A1, I would like cell A2 to have a drop down menu show a "0". If a "3" is entered into A1, I would like B1 to have a drop down menu show "1" or "2". If a "4" is entered into A1, I would like B1 to have a drop down menu show "3" or "4". Lastly, if a "5" is entered into A1, I would like B1 to show "5" or "6".
I don't know if this is possible without using VBA.
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.
Using 2007, I need to conditionally format a cell colour based on 5 other cell colours that have been conditionally formatted. The 5 other cells will be coloured either red or greem. What I want to do is have an overall status cell that would be show green if all of the other 5 cells were green, amber if 4 of the other cells were green and one was red and red if 3 or more cells are red.
View 4 Replies View Relatedi 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 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?
I have a spreadsheet which reports across different categories, but there's a lot of info. So simplify this, I've written a simple macro that changes the series range in an existing chart, so top level and by category. This works well apart from I'm using excel 2007, and some people aren't, so the default colours look fine on my version, but change to some awful colours on anyone with an earlier version of excel. I need a way of changing the colours to standard ones depending on which macro is being run.
I have tried running the following, but I get an error, basically saying the series hasn't been selected.
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).Interior.Color = RGB(255, 0, 0)
Is it possible to have a listbox with each line with a different colour?
View 2 Replies View RelatedI use this to read cell content, add some text/characters (ie. [ and ]) and change the properties of the complete cell
Sub COMMENT()
Worksheets("DVD Lijssie").Activate
If ActiveCell.Value 0 Then ' Change all in to ... ... ...
ActiveCell.FormulaR1C1 = ActiveCell.Value & " " & "]" & " " & "["
With ActiveCell.Font
.Name = "Arial Narrow"
.Size = 8
.ColorIndex = 16
End With
End If
End Sub
HOW can I change this vba-code so it leave's the content of the cell like it is and add some content with the use of let's say TexBox1 and ONLY use different font properties for the newely added content?
Is 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 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 RelatedFormula/macro/etc that would enable me to have content of a cell changed based on the content of another cell in the same row.
Example: cell in column D says "PSA" - so I would need the cell in column H for that same row to read "Radio"
I would need an entire sheet scanned to review for these occurrences and make the appropriate changes. I also would need the formula to be inclusive enough to scan for variations in column D cell content (PSA 1, PSA 2, etc).
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 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.
was looking if you could change a row of cells background colour if certain word is inputted elsewhere. in b2:f2 is data- was looking if, could these cells background colour change when yes is entered in G2? does not matter which is used, VBA or con formatting just cant work it out.
View 9 Replies View RelatedIs it possible to select only cells of a certain colour, then change these cells to a different colour? example
I want to make the dark grey columns a light green and the red columns a lets say yellow and the text black. Is this possible??
does anyone know of a way to amend this to add the cell to a range say "All_blue" to then copy and paste special values.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) ...
This question about conditional formatting appears to be easy. Lets say I have a column like the following, like I posted somewhere else: (not very relevant for this)
BGW
3UG
WWU
1BR
UUBRG
WUBRG
7UUGGRRR
How do I have all characters in this column be coloured accordingly to:
W = WHITE
U = BLUE
B = BLACK
R = RED
G = GREEN
So that 7UUGGRRR would automatically switch to:
7UUGGRRR
What I already tried:
- I applied conditional formatting to the column (actually, the cells interval, [C2..C8]) creating the rule: 'format only cells that contain -> specific text' -> then I added "U" as the text and told it to colour blue. As I predicted, this didn't work; the entire cell containing a single "U" will get blue, which is unwanted.
I've got 12 months worth of business accounts which have been downloaded onto a single spreadsheet.
These show negative and positive values. As I only want to work on the negative figs at the moment, how do I highlight the colour of the positive value cells or font to prevent any mistakes ?