I have five possible reps names in column A, when i type in "peter" i want that row up to column I to have a background colour of yellow, each rep must have his own colour. when i clear the name from column A the row should clear the colour. using conditional formating i can only get the first cell to get a background colour.
View 9 RepliesI have been working on a spreadsheet but I use autoformat for.
For example if the date in the cell matches today's date then it turns the text read and makes it bold.
I was wondering if there was a way to format several cells in a row if a certain criteria was met.
for example
A B C
1 Item Cost Quantity
2 Car £11,520 2
3 Bike £7,500 1
4 Tyres £50 4
Now for example if I set a condition whereas the quatity is greater than 3 then it would highlight the whole row.
So in the example above I would like to to highlight row 4 and Fill colour cells A4,B4 & C4 with a yellow background and Red Text and make the texted bold.
I have a spreadsheet with several freeforms. I would like them to change backgroundcolour, if the content in a cell is equal to another cell.
Basically I would like my shape "Freeform1" to change backgroundcolour to RGB (0, 180, 0) if Sheet1.Range ("A1") = Sheet2.Range("D3").
If it's not the same content in both cells, I would like the freeform to remain unchanged, that's RGB (79, 129, 189).
This should happend automaticly, so I don't have to click the shape to make it happen.
Is there a way to automatically conditionally format each column in each row, when either open, closed or cancelled is selecting from a drop down in the Status column. For example every cell is blank in colour, but if I select open in the drop down menu for row A2, the entire row will turn Red. Just wondered if there is a way instead of individually formatting every row separately. I would like to be able to copy the formula or whatever down the entire sheet.
View 2 Replies View RelatedI have to codes which work
View 3 Replies View RelatedI have figure A1 column which total amount accounting at cells A31. I need figure 360 as a benchmark calculation perfectly been done. In this calculation 360 well off inside cells A18 although actual number is 363. If we use Conditional Formatting when had enough total in cells the achieve 360, cells colour will be transformed to red. What formula which need I used to change no matter cells on A1 to A30 if sufficient study 360 automatically cells changing colour to red
View 5 Replies View RelatedI am wanting to create a function, callable from a worksheet, that sets the background colour for any given range of cells to a given RGB value. I am confused about the Color and ColorIndex properties. I'd have thought the following piece of code was trivial and would work but it doesn't.
View 4 Replies View RelatedI want to change the back ground colour of each row until G column when there is a text in the first cell. I have attached the excel that I want the background colour to be. Like suppose, F1 is the details of family 1, F2 is for family 2. I want each family to be coloured. The rows are not fixed for each family. If F1 details are coloured green, Fr is orange, F3 is green and F4 is orange again. And the number F1, F2, F3, F4 and so on are not ordered in ascending. They can be random. But I want the colour coding to be the same as what I have shown in the excel. I have so many rows like that. How do I do that using excel. I have tried conditional formatting. But it doesn't work.
View 2 Replies View RelatedIn the IAF tab, last one on sheet, I want to highlight a cell if on the Day tabs there is anything in column E, this will be text e.g. LAD, LE, E, AD, LC or NS, and in the same cell show the number that appears in column J of the day tabs, the excel sheet I'm working is to large to upload, if I delete some of the tabs all of the formulas go out of kilter. If Joe Blogs was at work on Day 1, the corresponding cell in the IAF tab should be yellow, if the supervisor monitored Joe's work on Day 1 the same IAF tab cell should remain yellow and show the number that is placed in column J on Day 1 for Joe Blogs.
I have managed to upload an amended copy of the file : Job PSS MASTER - v3 6 AMENDED.xlsx‎
when i pasted the data into an Excel sheet, the background will change to red and the font will turn to blue. Besides, the font in header will change as well. I can't find the way how to do it because this template is set by other person. I can confirm this is not done by macro.
View 9 Replies View RelatedI need a macro that will change the background colour of a cell depending on whether it equals a certain variant. Let me explain.
Cells D8 to N8 will either equal 1, 2, 3 or 4. Depending on the value of the cells on row 8 (D8:N8) I need the above cells on row 4 (D4:N4) to have a different background colour.
If the cell on row 8 is 1, the cell on row 4 must have a green background.
If the cell on row 8 is 2, the cell on row 4 must have a yellow background.
If the cell on row 8 is 3, the cell on row 4 must have a red background.
If the cell on row 8 is 4, the cell on row 4 must have a blue background.
Therefore, if D8 = 3, D4 will have a red background. If F8 = 2, F4 will have a yellow background. And so on.
I need a macro that when ran changes the background colour of the cell range D4:N4 depeding on the value of the cell 4 rows beneath (D8:N8).
How I can change the background colour of cell C:2 depending on what other cell i click? Below a sample of the code im using Basically when i click B:2 i want C:2 to turn grey with the text on top.... Then when i click B:4 i want C:2 to turn Red.....
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Address
Case Is = "$B$2"
Range("D2").Value = "No action needed connector is down" 'change this to suit
Case Is = "$B$3"
[Code].....
I have recently inherited a spreadsheet where information is tracked by using various background colours on cells for different meanings.
In order to clean up the data and make it usable I need to find the 1st cell in each row without the fill
Is there a way in Excel to display the first cell in a row which is not blank andwhich doesn't have a background fill applied?
I have a sheet that uses V lookups to return names, there are 10 diff names throughout the sheet it could return
I want to change the background colour of the cell depending on what the name is. the v lookup is run in collums A - L and 100 rows down
How can I code it so each name has its own background colour so the Cell changes according what the V look up returns?
I'm trying to set a conditional formatting to do the following:
If A1 is greater than 1, A2 will change its background colour to green. So the format needs to be in A2.
Basically in A1 is a page number that we have reconciled our bank statement to (labelled as "64" or "65" and so on), A2 has the end of statement amount.
Every month end we do a bank rec, the green cell is a marker to show what point we reconciled up to previous month.
I have some code below which looks at values added between a set range of cells. This works OK.
My problem is when i have to change the cell as it removes the background colour already set to certain columns ie Columns I, J, P, Q, W, X, AD, AE (these are coloured a light green).
The other cells in the range have no colour and so the code below works with no problem.
I have a column range,say (a1:a45) that has a 2 digit numerical value, next to that I have a column range, say (b1:b45)
I want to test subtract the value in b1 from the value in a1
if the answer is equal to or greater than 0 I want to colour the cell green.
if the answer is equal -2 I want to colour the cell yellow.
if the answer is less than -2 I want to colour the cell red.
I'm using this code below to change the background colour of a range of cells:
Code:
With .Range("I18:J18")
.Locked = False
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 9739376 'RGB(112,156,148)
.TintAndShade = 0
.PatternTintAndShade = 0
End With
I am receiving 'Object doesn't support this property or method' errors starting with the highlighted row. I'm unable to understand where I've erred. Note that range I18:J18 are merged.
Is this method an efficient way of simply changing a cell's background colour?
The problem is that the cells which need to change are locked and have a object group ontop of them and are not all in one row or column..
The cells which need to change are A4,A5,A6,B6,C6,D6,E6,F6,G6,H2,H3,H4,H5,H6,I6,J6,K2,K3,K4,K5,K6.
It sort of a theme changer for my spreadsheet incase people want a change and can do all of it with one click of a button or maybe click on a cell with the colour on it and have that colour copy itself to the range.
I need to input values rangeing from 0 to 5. When inputing I want to make the background colour change to a differant colour for each value. I can do it but only for data already in the spreadsheet.
View 9 Replies View RelatedI have a large list of students (Col a,b) who do several sports (up to 10) each, which are arranged as sport name (Col c) followed by score (col d), repeated on pairs of columns for the other 9 sports.
I need a macro to find the 4 highest scoring sports for each student in turn and to highlight the score and sport cells. I have tried to find permutations using the LARGE function but I cannot see how I can do this.
So I have a date in cell A1 for example.
Now if cell B1 is either blank, or more than 6 days from the date in cell A1 I want it to turn red.
How do i change the background colour in a list box?
An example is attached.
how can I change the following code so that the cell background colour remains the same. the below code unlinks the hyperlinks but deletes the backgroundcolour also of all the cells.
Code:
Private Sub Button3_Click()
ActiveSheet.Hyperlinks.Delete
Range("A7:W100").Select
[Code].....
I have been assigned a task to create one table by counting how many cells are in red, yellow, blue, white colour.
red: indicate the certificate which will expired within one month
Yellow: indicate certificate which will expired within 2 to 3 month
blue: indicate certificate which expired
white (without any colour): indicate certificate that will expired in the future which more than 3 month
The table shall consist of "non-expired cert" - red and yellow; expired cert and total cert.
I would like to know whether it is possible to change the background colour of items in a dropdown/combobox on my userform?
I have a series of times at 15 min intervals and i want, if possible, to distingish between 'working hours' and 'non-working hours' by making the background of these numbers 'grey'.
Maybe looping through from 00:00 to 07:00 i.e 28 times (28 x 15min intervals)?
Is it possible to remove all gridlines (except in a table) and change the background colour to plain colour (except the table) in Excel 2010?
View 2 Replies View RelatedHow to make a text background on editable cells and print without text background?
View 4 Replies View RelatedI have a data set of 300 points and i want to increase my data range by a factor of 10 .
For example if i have Column A with master points A1=10 A2 = 20 A3 = 30 .
I would like to be able to automatically enter in the same about of new rows between each point . Then calculate there difference between the master points and populate the new rows in sequence . For example The difference between A1 and A2 = 10. So the new A2 would be 11
I would like to have A1=10 A2=11 A3= 12 A3=14 Etc as the new cells.
Is it possible to search Excel by cell colour, i.e. font colour...and then change the said colour to another one?
View 3 Replies View Related