I need to write a code that will take a range of cells and combine them into one cell in a different sheet.
The problem is, the cell range is defined by formatting - there is a top border and a bottom border, and that's the only way to really define the range.
I'm trying to format a range of cells based on a single cell. Below is the code that
I am using:
Private Sub Worksheet_Change(ByVal Target As Range) ...
I have a date in a cell that when it is less than today() (so yesterday's date) would turn red and the 5 cells to its immediate right would also turn red, if the date is still current then it would be green and so would the right hand cells.
View 4 Replies View RelatedI have created a speadsheet for a tournament that lists the team names down column A (leaving a heading row). I have a formula in the top row which copies each row heading to a column heading 2 columns wide (to be able to record a result)
Formula: =INDIRECT(ADDRESS(ROUNDUP((COLUMN()+1)/2,0),1))
This way it will be possible to keep the results of the tournament on a grid. I would like to put in conditional formatting that meets 3 criteria...
1, it must fill every second row but only fill cells that have column and row headings (ie. rows that will have content in them which will be determined by the number of entrants). eg. if i have 10 teams then every odd row from row 1 to 11 will be filled. If 40 teams register then every odd row from 1 to 41 will be filled. 2, the fill needs to only fill cells that have column and row headings eg. if 10 teams register then every odd row from column 1 to 21 will be filled. Then 3, If the row heading and column heading are equal then it need to gray out or fill a different colour (or lock cell contents to empty if possible) but again, only fill cells that have column and row headings.
I have 2 rules.
Rule 1: =(ADDRESS(1,EVEN(COLUMN()-1)))=INDIRECT(ADDRESS(ROW(),1)) PROBLEM:
It needs to only fill cells that have a column and row heading which it doesn't, it fills the correct cells untill there is no column heading then fills every other cell in columns beyond the last column with a heading.
Rule 2: so far this works:
=AND(MOD(ROW(),2)>0)
but when i try to restrict it to cells with row and column headings it doesn't work. This is what I had:
=AND(MOD(ROW(),2)>0,IsEmpty(INDIRECT(ADDRESS(ROW(1),COLUMN()))"")=FALSE) OR =AND(MOD(ROW(),2)>0,CELL("contents""",ADDRESS(ROW(1),COLUMN())))
I have two sheets (Results and August) with numbers in column A, I want to change the color of the cell on Results if its contents matches the number on August. I was feeling adventurous today, so I tried writing some VBA code, but it changes the color of ALL the cells. What am I missing?
Sub FindMatches()
'Compares student numbers on Results sheet to those on August sheet; if match is found then highlights the student number
Dim Sht1Rng As Range
Dim Sht2Rng As Range
Set Sht1Rng = Worksheets("Results").Range("A1", Worksheets("Results").Range("A65536").End(xlUp))
Set Sht2Rng = Worksheets("August").Range("A1", Worksheets("August").Range("A65536").End(xlUp))
Set d = Nothing
For Each C In Sht1Rng
Set d = Sht2Rng. Find(C.Value, LookIn:=xlValues)
If Not d Is Nothing Then
Sht1Rng.Interior.ColorIndex = 10
Set d = Nothing
End If
Next C
End Sub
I suspect that I shouldn't be using sht1rng.Interior.Colorindex, as I think that may color the entire range? What should I use instead?
I had a list of people on sheet1 of my workbook, and on sheet 2 a table with a list of names and their department.
I started off by wanting to use VLOOKUP within the conditional formatting feature of Excel to find anyone who worked in "finance" in sheet 2. I could get this formula to work on the sheet but as soon as I put it into the conditional format feature it wouldn't work.
Trying to find a work around I added a column in sheet 1 where I inputted the VLOOKUP formula and caused it to display "1" if the person worked in finance. It looked like this:
A: Person's name
B: Value of 1 or 0
The idea was to hide column B and use conditional formatting to say "if column B2 = 1 colour column A2 blue". Now I could get this to work on a single row but couldn't make it work over a range, ie. "if cells A2:A150 have a 1 in the column next to them colour the relevent cell blue"
I feel I'm missing the obvious. I found quite a few answers online but non actually specified how to get the conditional formatting to recognise the relevent cell it should be looking at in a range.
1) How would I get conditional formatting working over a range
2) Whether it is possible to use VLOOKUP within the conditional formatting function of Excel.
Attached is a workbook. I have manually highlighted the cells to give idea of what I would like the outcome to be.
Moonah OPS sheet.
In Row 5(Shift times), under the drop down menu in cells B5:J5, there is a range of Shift times.
And for the purpose of this I have selected
B5 as 0800:1621, manually highlighted B11:B27
C5 as 06:00, manually highlighted C7:C24
D5 as 07:00:1521, manually highlighted B11:B27
E5:I55(RDO, REC, SICK, Travel) can remain as manual input if it is to much to work out.
Is it possible or just a pipe dream, to use some method to automatically highlight a range of cells based on the C5:J5 'time'?
If the time is an actual shift time, the colour will be green and dark green writing as default, then we can allocate jobs/training/meetings and manually colour them.
Daysheet DAY MONTH YEAR template - Highlight.xlsx‎
I am working with the Conditional Formatting, which is fine for one cell. Here is what I am trying to do: IF cell in $A1 = 1 then bold $B2:$M2 and apply solid line border to top of cell ranges. I have tried conditional formatting but it only formats the cells in column A. And I can't seem to find a BOLD statement for the cell formulas.
View 2 Replies View RelatedI have a range of cells where each row relates to an employee.
In Cells (Columns H:L) I want the range of cells on that row to turn blue if the user puts a "H" in one of the cells.
Also it will need to be adapted for "O" , "Y", "X" - each with different colours.
This needs to be repeated on each row for each person.
what i would like to do is change the fill colour of D68 if the word Air appears with in D5:D65?
View 6 Replies View RelatedI 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‎
how to apply conditional formatting via VBA to a range of cells based on input from another range of cells. Obviously this would be easy in Excel 2010, but I'm still using 2003 at the office and it needs to stay in this format to be readable by other users:
For cells M8:EK8, my conditional formatting
condition 2: Formula Is =AND($E$8>=M2,(($E$8-$D$8)>=(N2-$M$2))), color index is 40
condition 3: Formula Is =AND($F$8<=M2,$G$8>=M2), color index is 39
I want to add:
condition 4: Formula Is =AND($H$8<=M2,$I$8>=M2), color index is 40
condition 5: Formula Is =AND($J$8<=M2,$K$8>=M2), color index is 39
and so on
The cells in the range M8:EK8 are blank, they only get colored based on input added to D8 to K8. If there is no input, then the cells should be uncolored.
resource tracking ex.jpg
Summary of performance of various products against target is as follows,
Product vs Target
Color Code
Result
CH4OH
Green
1.0
[Code] ........
I need the final result automated as follows,
If 2 green of the 4 products, then final result Gree
If 2 Amber of the 4 products, then final result amber
If 2 Red of the 4 products, final result Red
Is there a way to automate this?
I was looking to use the VBA conditional formatting script posted on OzGrid and was curious if it would be easy to make some slight modifications.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Select Case Target
Case 1 To 5
icolor = 6
Case 6 To 10
icolor = 12
Case 11 To 15
icolor = 7
Case 16 To 20 ..........................
I can see that Case is the number range referenced to change the background...However, would it be possible to have it read a cells value as the criteria for the color change? Currently, I would like it to reference values in range I2:M2 and anything matching those cells in range A4:E28 change background to icolor = 30. I'm just not sure what to replace Case with to make it refence cells I2:M2
I have a workbook that I've built for a project. I've attached a sample workbook. What I'm trying to do, for the entire sheet or workbook if possible, is turn any Cell with a percentage of 30% to 49% yellow and any cell with a percentage of 50% or more Red. I would also like to move the ID's of the variable cells, for example Id number 9922, to the cells beside the description of the rows, Affected would be an example, if the information contained in the same row as the ID meets with a set of variables.
For example I only want the ID's moved if they correspond with IDsub 1-25. One more thing, the people who will be using this spreadsheet will be copying data from a website when it is imported it does not insert the values as numbers. I would like to format the cells, in example workbook they would be any of the cells labeled ID IDsub Variable or Number, so that anything put in that cell will automatically be converted to a number.
I should also probably add that the formatting will be done on approximately 80 rows a sheet with 47 sheets.
I currently have a spreedsheet which has a conditional formatting cell where if the figure is above 1 is green and below red what i am trying to do is get a sum to calculate based in its color?
I.E. I have a value in cells F7+F25+F27+F29 i then have a value in F17 but only want this to be included in the total calcualtion if the figure in E15 is equal/above the value of 1.
I'm trying to visually format my spreadsheet in a way that lumps customers together. Excel gives you the automatic "every other line" two tone variation.
(blue)
(blue)
(light blue)
(light blue)
(blue)
(blue)
[code]....
I've sorted by data by customer name. I want to format all Customer A rows as (blue), Customer B rows as (light blue), customer C (blue), customer D as (light blue) etc. With hundreds of customers and typically between 1-5 entries for each customer, this an impossibly time consuming task. Is there anyway to automate this process?
Customer A (Blue)
Customer A (Blue)
Customer B (light Blue)
Customer C (Blue)
[code]....
I basically have a spreadsheet with several columns A-E. Column A contains a number from 1-6. I want to format the contents of the cells in column B based upon the number in column A.
An example:
If A3 = 1
Then I want B3 to be - Font size 10, bold, underlined, indented twice.
I need the code for formatting to be edited, so that it work dynamically for any range
VB:
Range("B16:L16").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
[Code] .....
Here the columns "B" and "L" shall be constant, but the number 16 may vary depending upon the number of lines in the sheet.
I would like to restore the currency format including the $ and three decimal point commas.
=MIN('2Data'!E2:E500)&(" - ")&MAX('2Data'!E2:E500)
I have a list of names that belong to a particular group. What I want is to conditional format a sheet so that if a name is entered that does not belong to the group it is highlighted red. Don't really know how to do this though?
View 4 Replies View RelatedI have a spreadsheet that has a table listing employee names in the far left hand column (A1, B1, C1, etc.). The table has a simple border - black line throughout.
I need the entire row for the employee to change to no border if the employee's name is deleted.
Using the following conditional formatting formulas:
=(isblank(p99)) then the formatting of (applies to) C99:O99 will be in red and bold
=(d99="Prod.") then the formatting of C99:P99 will be blue and bold
but the results are not as expected. Is something wrong with these formulas?
A collection of cells as been set as range and named "ShowPrice_"
I want to set the number format for these cells to be "##0.000" using VBA
Code:
ShowPrice_.Format ("##0.000")
Martin
I'm writing a macro that’s taking user input (from a form I developed) and will generate a chart according to the input.
One requirement for this chart is that the background alternates fill colors between gray and white. Every four columns need to be either gray or white. Based on the duration the user entered, the width of this chart is created. The length (the number of rows) is constant.
I've tried different loops (for, if, do - you name it I tried) This loop is just out of my league. Any and all help would be greatly appreciated.
Summary of Facts:
- Starting Point of Chart is B2 (the top left corner of the chart will be in B2)
- the number of columns is dynamic (based on user input)
- The rows are constant are (2 through 16)
- Every four columns need the fill color to be either gray or white
I've got an Excel VBA Macro that takes data from a UserForm and TextBox and inputs a value into a cell on a spreadsheet.
I'm having issues when this value is input into a cell that's formatted for "Accounting". I've tried all the tricks I can think of to get the value to display correctly, but the correct currency formatting is only displayed when I manually select the cell and hit the ENTER button.
The value of PriceBox is derived from a TextBox input by the user (9000 for example).
Range("D16").Value = PriceBox
In Excel, I want to do conditional formatting on a cell based on the value of another cell. For example: in cell A1, I have text. I want this cell to have a grey background if the value of cell B1 is less than or equal to 1/31/14 and to have no fill (default) if it is greater than that date.
View 8 Replies View RelatedOn the attached worksheet I'm looking for the squares in F4:F6 to be coloured corresponding to the colour names in D4:D6 using conditional formatting. I just can't get it to work today.
View 1 Replies View RelatedI have a worksheet which has the years 2001 to 2013 on in row1. I am trying to find a way of highlighting the entire row in green if a specific year contains an "a" (Marlett, tick). The complex part is that I would like the specific year to be determined by an entry input into cell A2 on sheet 2. See Attachment for reference.
View 2 Replies View RelatedI am working in a table where I would like the whole row to highlight based on the value in one of the cells. These rows of data will be updated regularly, so the formatting has to change with the data.
View 13 Replies View Related