Colouring Specific Cells Using VB
Dec 22, 2006
how I might be able to colour certain cells in a row a specific colour when a specific value is input.
For instance:
The value "A" is put into row A1. A1 and C1 cell colours change to green
The value "B" is put into row A2. A2 and C2 cell colours change to orange
etc. (not sure how many colours yet)
I sort of have a script set up, but there are certain things I dont know how to do. Like target the specific cells that need colouring.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 0 Then Exit Sub
Select Case Target.Value
Case "A"
Target.Interior.ColorIndex = 1
Case "B"
Target.Interior.ColorIndex = 2
Case "C"
Target.Interior.ColorIndex = 3
Case Else
Target.Interior.ColorIndex = 0
End Select
End Sub
View 9 Replies
ADVERTISEMENT
Feb 17, 2010
I have a column for each month where the dates are entered on rows. Is it possible I can color a cell if the date entered in a cell for that month is 15 days past the end of the month. Like, if in a January column I enter a date on one of the rows as Feb.20, then the cell fills with red colour. I tried doing it using conditional formatting, but don't know how to say 15 days past end of the month?
View 5 Replies
View Related
Oct 10, 2008
I have a spreadsheet that contains data I import from various sources. Once the import is complete I need to colour various cells based on their contents. Normally I'd use conditional formatting, but there are more than 3 conditions so I need to use VBA (preferrably that i can turn into a macro and assign to a button to click once I've finished the imports each time)
eg: Everytime a cell content is the word "RED" , colour it red - then the same with various other colours.
Google has found me a change event piece of code but it doesnt work when you're cutting and pasting some info.
View 9 Replies
View Related
Dec 4, 2012
I have a rather large database that I'm trying to automate colours in, based on data that is inserted into Column 'D'. I've used the following code (with some success), but it colours columns that I don't want to colour:
VB:
Sub KeyCellsChanged()
Dim Cell As Object
For Each Cell In Range("D1:D5000")
[Code].....
That works fine - but the problem I have is that I only want to colour columns A:N, Q, T, V, AB:AE, etc. (random columns and not always together). I have already tried to replace parts (as follows), but get all sorts of errors (in particular Run-time error 1004):
VB:
If Cell = "Rabbit" Then
Cell.Range("A:N,Q, T, V, AB:AE").Interior.ColorIndex = 42
Is there someway that a line of code can determin which cells to colour in the row, or alternatively code that will colour the columns a specific colour and make them stay that colour when the first code above is used?
View 3 Replies
View Related
Mar 28, 2007
I'm trying to automatically shade a number of cells dependant on the value in a single cell. ie. If D2="quote" then I want A2:Q2 to be yellow. If D2="Design" then I want A2:Q2 to be green etc etc. I have about 6 options in all of what D2 could be.
I then want to repeat this for about 200 rows.
View 7 Replies
View Related
Feb 28, 2014
I am currently using a piece of code (within a bigger piece) that colours the dates in two columns based on how close it is to the current date. It works fine but sometimes the sheet it is looking in has over 3500 rows so it is taking for ever (well over 6 mins)
The code is
HTML Code:
Sub ColDate() 'date case opened
application.ScreenUpdating = False
Dim MyRg As Range
Dim F As Range
Dim DateDiff As Long
Set MyRg = Range("g1:h" & Range("H" & Rows.Count).End(xlUp).Row)
For Each F In MyRg
[code]...
View 1 Replies
View Related
Jul 23, 2006
auto colouring cells containing formulas
View 2 Replies
View Related
Apr 16, 2006
I'm a teacher and would like to show progress in my pupils achievements by colouring coding cells in excel. For each subject they are given a level, e.g. 2b. If they show progress, they would move up, i.e. to a 2a. The whole progress scale is shown below:
wc,wb,wa,1c,1b,1a,2c,2b,2a,3c,3b,3a,4c,4b,4a,5c,5b,5a,6c,6b,6a,7c,7b,7a
To make the spread sheet visual i would like colour code the cells depending on whether progress has or has not been made. If they go up (e.g. 2b to 2a) i would like the cell to turn green, if they go down (e.g. 2c to 1a) i would like the cell to turn red. If not progress has been made then the cell can stay uncoloured. Each cells colour would only be dependent on the cell directly to the left.
View 7 Replies
View Related
Mar 12, 2014
I am trying to get a row of cells to highlight a percentage based on a date range
Below is an example of what my spreadsheet will look like, very simple for managers to read and understand but I am stuck on how i can get this to display the right way.
In the example i would need the Jan column to colour for a certain percent for 21 days and continue to feb for 26 days. Im not sure if this makes sense but this is what they are asking for. Colour bars to simple show the percent of days off each month.
Name Start Date End Date Jan Feb Mar
Dale 11/01/14 26/02/14 21 days 26 days
I have attached the spreadsheet for an example : Book1.xlsx‎
View 3 Replies
View Related
Aug 7, 2006
Is it possible to get the following to work.
If cell A2 has the number 5 in it (as a result of a calculation in that cell), is it possible to get the next five cells in that cell to colour themselves in?
Obviously, as and when the value in cell A2 changes, i need the number of cells coloured in to change as well.
In addition to this, the rows that this will happen in will vary week to week so i'm not sure if VBA is the way to do this.
Also would there be any problem with negative figures as when that happens i don't want any cells to colour in at all.
View 9 Replies
View Related
Jan 27, 2009
Just trying to work out the best way to be able to determine autoshape colours using cell values. For example I have 12 shapes that need to coloured either Red, Green or Orange depending on a specific cells value, 1 for Red, 2 for Green and 3 for Orange.
I have sort of managed to successfully complete this for 1 of the shapes, by using the RGB Fill option (Using 3 = Red, 4 = Green and 45 = Orange). But cannot for the life of me work out how to do this for multiple shapes using different cell triggers. Below is the code that I currently have and the spreadsheet that is linked to:
View 5 Replies
View Related
Oct 18, 2011
How do i have colouring to a reference cell what i mean is that on certain formula i get BUY or Sell on that cell. As per the conditional formatting i have made the colour change BLUE FOR "BUY" & RED FOR "SELL"
On second sheet i link the above TEXT., but the COLOUR OF THE TEXT Does not come.
Does CONDITIONAL Formatting come with reference cell.
View 7 Replies
View Related
Aug 2, 2006
I have a gone through quite a few topics in this forum.....but couldnt find answer to my question....may be because its too basic. I have a worksheet with 10 columns. I am trying fill colour in the rows that have "text" in column J (in other words.....their column J is not blank). I figured out how to fill the colour in the cells in column J when they are empty, but cant figure out how to colour the entire row from A:J.
View 4 Replies
View Related
Jul 28, 2009
I've been trying to get a macro together which would colour all the tabs in my workbook based on whether or not a certain value exists in a certain cell. In this case the cell is C7 and the word there is "elective class:".
View 7 Replies
View Related
Aug 2, 2008
I currently have some code which reads a stock file, adding up the stock for each individual item (which may be in one or more locations in the warehouse) and then placing the total stock count in a cell with the detail (of how much stock is in each location, the location and the expiry date) and a separate line for each location, in a comment.
The comment is constructed one line at a time as the code finds the stock records, see code below:
With shtWorking.Range(strStartColumn & intRow + 2)
oldComment = .Comment.Text
.Comment.Delete
.AddComment oldComment & vbNewLine & strComment
.Comment.Shape.TextFrame.AutoSize = True
End With
All this works very nicely, but I want to colour individual lines depending on the expiry date. This can be done manually in Excel, but I cannot find a way of colouring individual lines of text in a comment using VBA.
So, does anyone know of a way I can colour individual lines of text in a comment, using VBA?
View 9 Replies
View Related
Jan 28, 2014
As you can see from below I already have a spreadsheet which updates the fill colour of cells based on a certain criteria. This was initially set up in Excel 2003....I have now moved onto 2010 and want to use a certain colour based on it's RGB value. I tried as you can see from below, the part which is commented out (as it didn't work)
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Long, lngIndex As Long
For n = 6 To 842
[Code].....
View 3 Replies
View Related
Feb 21, 2010
I have "borrowed" the below code from a previous solved post, however am unsure how to adapt it.
View 7 Replies
View Related
Nov 7, 2008
I have a protected template and unprotected source worksheets - - - what I would like to happen is for the macro to start and if the source worksheet cell B3 equals "Report Total" then stop - otherwise copy template worksheet then copy 6 specific cells from the source to paste values to specific cells on the newly created worksheet (B_ to C7, D_ to I7, E_ to C9, F_ to K9, A_ to C11, M_ to K11 and then K13=F13-30)
After that then start all over again unless the next row’s cell (B4, B5, B6, . . .) is "Report Total" then stop - - - the row count could be from one to a couple hundred.
Here is what I have so far but I know that with each copy the name will change and as it goes down the source file each row will change and I also need help with that.
Sheets("ee template").Copy After:=Sheets(3)
ActiveSheet.Unprotect
Selection.ClearContents
Range("I7").Select
Selection.ClearContents
Range("C7").Select
Sheets("source").Select
ActiveCell.Offset(0, -11).Range("A1").Select
View 9 Replies
View Related
Oct 9, 2009
Here is what i am trying to achieve. If the date 2/20/2010 is located at F53 & the cell next to it at H53 is populated with a number between 1 & 16, then i want the cell at J11 (42 rows further up) to auto populate with the number 1. When this occurs the cells beneath this, from J12 to J52 should also auto populate with the with consecutive numbers from 2 to 42. Would also like to see the cells with numbers 1 to 28, automatically format to orange & the cells containing numbers 29 to 42 automatically format to yellow. I plan to have this condition repeat several times later in the year, at dates that are to be decided. When these dates are decided i want to be able to enter a number from 1 to 16 & next to the date & all of the above automatically occurs.
View 14 Replies
View Related
Apr 30, 2007
I'm trying to use a conditional format on a range of cells so that if there is more than 1 "A" contained in any given row, the cells containing the "A"s are highlighted (e.g. red)
I've tried:
=COUNTIF(B2:K4,"A")>1
but it doesnt work & now I'm stuck.
View 9 Replies
View Related
May 23, 2014
I am trying to build a staff roster. The staff rotate over a 4 week cycle. the name of the staff member, and their shift needs to be looked up from the key then matched with the particular week. the name and shift then need to populate specific cells.
I have attached the worksheet so you can see what i am trying to achieve.
View 2 Replies
View Related
Feb 7, 2013
I am using Excel 2010 and basically i am trying to fill a range of cell with a green color if any value was enter in a specific cells. Example: I would like to fill range: A10:c13 with a green color (regardless of the cells content in this range) if a value was entered in cell C10 or C11 or C12 or C13.
I've tried conditional formatting but unfortunately I'll have to apply formatting for every cell and for a range of over hundred cells is not efficient.
View 7 Replies
View Related
Feb 1, 2010
I'm working on a project for my company. We make plastic tanks and for quality control we want to start recording the thickness of the tanks in different areas/zones of each tank.
Attached to this message is an Excel sheet that I've been working on. From "Sheet 1", it records inputted thicknesses into WorkSheet "1098". On the top of "1098", it shows all of the recordings, and just below that are the "10 Most Recent Entries".
Right below the "10 Most Recent Entries", there are formulas to calculate the Min and Max Values. Whenever a new entry is recorded, the selected cells for the Min and Max formulas change. Is there a way to force the cells to always stay the same?
View 3 Replies
View Related
Mar 18, 2014
I have a couple of spreadsheets that has several columns each containing several hundred thousand rows of codes. To quickly analyze this data, I am trying to come up with a conditional formatting formula to highlight the respective cells when specific values occur next to each other. A particular code will show up in many cells, but the code that is the respective adjacent cell is always different. I need to know when row A contains, for example, '9928559' and row B contains '36415RT'.
View 3 Replies
View Related
Jan 31, 2013
I have a similar question regarding coloring of empty cells between two cells with specific numbers as posted in below thread : [URL] .....
This is exactly what I want. But here I have some problem with formula.
=COUNTIF(A1:Z1,1)=1
Above formula could pick cell value 1 and color the empty cells in a row between the cells having value=1. But if I want to use the same formatting for other numbers like 2 or 3 it doesn't work.
For example in row(A1:Z1) I have A1=1, D1=1, and F1=2, K1=2, and O1=3, U1=3
I required multiple conditional formatting that could color the cells based on values first from A to D then from F to K and finally from O to U.
I'm using above formatting it works with cells having values 1 but its not working for 2, 3 or any other cells values.
I have already tried replacing 1 in above formula with 2 and 3 but it doesn't work.
View 1 Replies
View Related
Jan 17, 2010
I am seeking a formula which can be “dragged” which will copy certain cells upon a row onto specific cells on another tab, when i drag at present it skips rows from tab 1?
Eg;
Tab1 B12 to Tab2 C8 & Tab1 E12 to Tab2 C12 & several others
Tab1 B13 to Tab2 K8& Tab1 E12 to Tab2 K2 & several others
Tab1 B14to Tab2 S8& Tab1 E12 to Tab2 S2 & several others
Example spreadsheet attached
Simple for all you Excel genius’s, I am only just getting to grips with excel & cant wait to know as much as all of you!
View 9 Replies
View Related
Apr 14, 2014
I am trying to find a solution for highlighting cells in a column that are repeats, ie. >3. I also need these cells to only be highlighted if the adjacent cell in the next column contains specific text. I have tried using conditional formatting with a countifs formula to no avail.
View 2 Replies
View Related
Jan 18, 2014
I am setting up a charity accounts sheet, that will also calculate money raised from Raffles, so each raffle will have a name, I can do this by matching the amount with a description but wondered if I could calculate it by looking for specific words or reference in a cell
Sum all cells that the adjacent cell contains the reference "Raffle 0001" but not "Raffle 0002" this needs to be calculated on another cell
Sum all cells that the adjacent cell contains the reference "Rescue" this cell could contain other text as well, so could say "Rescue for Sweetie by Laura Herarty" or "Rescue for Bliss by Jane Telford" all need to be calculated.
View 4 Replies
View Related
Feb 11, 2014
How do you limit a cell to only display fractions in sixteenths, eighths, quarters, and halves?
I'll give an example.....we use excel to cut list doors in our cabinet shop. On a three panel door it will spit out a fraction of 1/12" (or 5/12, or 7/12 etc), but this looks a lot like 1/2" and the guys frequently cut them the wrong size.
Is there any way to limit a cell so it will only display in these fractions 1/16, 1/8, 1/4, 1/2 etc? and I don't mean by just saying to format in sixteenths, because if I do that then it displays 12/16" when I want it to say 3/4".
I basically want it to round to the nearest sixteenth, but I want it to display to the closest sixteenth, eighth, quarter, half etc.
View 3 Replies
View Related
Feb 11, 2009
this seems like such a simple thing to do but I just cannot suus it out. Basically I want to Highlight a cell depending on another cells value:
ie. I have a value in Cell A1 and a value in Cell B1, in Cell C1 I have an IF statement that dsplays the word 'NO' if the values in A1 & B1 are not equal. In Cell D1 I have the word Fault. I would like to highlight Cell D1 if the Cell C1 displays the word 'NO'
View 3 Replies
View Related
