Using 2003, I'm trying to do the following, data starting in A3, and ending in L3:-
- If A3 is blank, cells have no format
- If A3 has a value, with A4 blank, fill is light blue and bottom line solid, top line dotted
- If A3 has a value, and A4 does too, fill is light blue, top and bottom line dotted
- If H3, which is a date field, is greater than today font is black, if less than today, font is red
I have these light blue lines showing up on my sheet. I have "not boarders" selected and they do not print - but I can see them on the screen. Does anyone know how I can get rid of them?
View 3 Replies View RelatedI am trying to convert the following macro to a Worksheet (section change) format, but cannot figure out how to have the cells B87:M150 change based upon the text (red, green, white, amber, na) in O87:o150.
Sub ordinate()
Dim r As Range
For Each r In Range("o87:o150")
If r.Value = "RED" Then
r.EntireRow.Interior.ColorIndex = 3
ElseIf r.Value = "AMBER" Then
r.EntireRow.Interior.ColorIndex = 44
ElseIf r.Value = "WHITE" Then
r.EntireRow.Interior.ColorIndex = 2
ElseIf r.Value = "GREEN" Then
r.EntireRow.Interior.ColorIndex = 4
ElseIf r.Value = "NONE" Then
r.EntireRow.Interior.ColorIndex = 2
ElseIf r.Value = "NA" Then
r.EntireRow.Interior.ColorIndex = 15
End If
Next
Range("N86:IV132").Interior.ColorIndex = 2
Range("A86:A132").Interior.ColorIndex = 2
End Sub
I'm trying to create a dashboard with traffic lights displaying the status of a task. My manager wants a to be able to pick a color (Red, Yellow, Green) from a drop down menu and then have that be displayed as a red, yellow or green traffic light. Using the conditional formatting option, I'm only able to use numerical values or a formula. I'm hoping there's a way to either create a drop down list with the 3 different colored traffic lights or a way to use VBA to have the conditional formatting search by text instead of numbers.
View 2 Replies View RelatedI am wanting to have conditional formats so the cell fill in a range on sheet 1 will change to:
- red if the value of a cell on a seperate sheet (date) is greater than 18 months old
- yellow if the value of a cell on a seperate sheet is 'In progress'
- green if the value of a cell on a seperate sheet (date) is less than 18 months old
This might be a simple answer but... if A1 had the date 1-Jan-2010, what formula would I used, within conditional formatting, to make that cell turn blue once 90 has passed? So the cell should turn blue on 1-Apr-2010.
View 10 Replies View RelatedWhen printing, how to automatically add a line at the bottom of each page? I don't want to make the line physically there. Just show the line in the page when printed.
View 2 Replies View RelatedI use this code to start at the the top of the column going down, but the way my data is setup, I need it to start at the bottom and work up.
Sub fillBlanksI()
'Declare variables
Dim FR As Long, LR As Long, i As Long, FillVal As Variant
'Stop screen flicker and speed up code
Application.ScreenUpdating = False
'Find the last row of any column in the sheet
LR = ActiveSheet.UsedRange.Rows.Count
'Loop from row i downwards until we find a value - this is the first filled row and assign its value to FillVal
I am using the following macro to set print area which I found on the net. I am trying to modify it as I would also like to place a border line on the bottom row but cant find anything to assist me despite searching through several threads.
Dim myrange As String
With ActiveSheet.Range("A:A")
myrange = .Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Address
End With
ActiveSheet.PageSetup.PrintArea = "$K$1:" & myrange
End Sub
I have a vba code that auto-fills, based on going top to bottom.
But now my data starts at the bottom ( LR=last row).
I basically need something that says...
If Last row-1 is blank, then enter last rows value.
then to go next row-1
else
to to next row-1
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
My macro works fine but I'm interested in seeing if I can speed it up. What I'm doing is starting at the bottom and comparing it with the row above and if they match in font color it will fill the top with orange and delete the bottom. This works but usually takes several minutes.
Public Sub ADMINCompareList()
Dim varTest1, varTest2
Dim lng As Long, i As Integer, iTest As Integer
Application. ScreenUpdating = False
Worksheets("ADMIN").Activate
For lng = ActiveSheet.UsedRange.Rows.Count To 2 Step -1
If Not Range("M" & lng).Font.Color <> Range("M" & lng - 1).Font.Color Then
Goto newrow
End If
varTest1 = Intersect(Range("J:W"), Rows(lng))
varTest2 = Intersect(Range("J:W"), Rows(lng - 1))
For i = 1 To 14....................................
Possible to create a macro that would detect end page (jumping from page 1 to page 2 for instance) and add a bottom border at the last row of the page.
I have attached a sample where I highlight in green the end of the page and added manually the bottom border (I did it only for the first end pages).
Iam studying civil engineering and iam trying to make an excel sheet to calculate reinforcement required for solid slabs
to calculate the reinforcment first you should calculate positive and negative moments along short and long directions with dead and live loads
what iam trying to do is creating 2 lists the first list is to choose the RATIO of Short Span(a) divided by Long Span(b)
The Second List is to choose a case out of 9 cases of possible connections
the idea is to choose a ratio from list 1 and a case from list 2 to return the values of crossponding moments
The table is attached
I have a list that looks like this:
1. Here I have som formatted text.
2. Here too.
10. And here.
110. As well as here.
The content of each line is in a single cell.
How do I go about automatically chopping off the number, the dot and the space before the text, but retaining all formattings?
working on a sheet I have one col that is formated for dates (yyyy-mm-dd) this is fine if you enter a date from the 1900's or 2000's but enter a date from the 1800's and it's a no go!
example:
Enter 14 jul 1941 and it formats 1941-07-14 no problem
Enter 14 jul 1841 and you get nothing just 14 jul 1841 no formating at all. And this is the same for all the date formats.
I'm using Excel 2000 and I have downloaded a report from another system. The dates come in for example in the format 10.06.2008. Using the replace functionality I can change this to 10/06/2008. However, when I do this using VBA
Range("L49:L300").Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
it works on most but some of the dates get switched to 06/10/2008.
First off I have an excel sheet that I have split into two windows. excel sheet.jpg
I am looking for a formula that will change the bottom sheet number a color if it exists on the top sheet.
I am trying to conditionally format the top middle and bottom thirds of a range of data. Problem is, that the range needs to be flexible as sometimes there may be a maximum of 36 cells with data, but sometimes there may be less (so there are blank cells in the range that need not be counted). The methods I have tried always include the blank cells, and so it is not equally formatting the thirds (as it includes the blanks cells as part of the bottom data)....
Here are the 2 methods Ive tried so far using excel 2003)
Top 34%:
=IF(INT(COUNT($D$3:$D$38)*34%)>0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*34%)),MAX( $D$3:$D$38))0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*67%)),MAX( $D$3:$D$38))0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*100%)),MAX( $D$3:$D$38))
hi i have a formula in a module that show a date as such
"ddd d" and this shows up as say "fri 6"
how can i get it to show up "f 6" or maybe "fr 6" and not have to use the whole three letters
:o
I'd like to import some dates into some textboxes on a userform.
I'm also going to let the user edit these, however... what i'd like is for the user to be forced to:
1) enter a date in the format dd/mm/yy
2) Enter a valid date (eg, not feb 30th)
I have a worksheet with conditional formatting applied. My formula works to change the cell color, font color, and strike-through the font if a "yes" answer is indicated. It all works well EXCEPT that the font color and strike-through ONLY apply to the top line on my cell.
Each cell has a title line, and a few paragraphs in it, with separations between the title line and the paragraphs. I kind of like having the title line a different font, but when it's time to strikethrough I want ALL of the text struck through.
I have a worksheet with several columns of data. In Col B are part numbers, in column D are dates. I wish to search through the data and remove all lines with duplicate cells in column B EXCEPT the line which has the latest date in colum D. I should then be left with a sheet that has no duplicate cells in column B and the latest date in column D.
View 9 Replies View RelatedI am using Excel 2007 and having some trouble with conditional formatting an entire line.
What I currently have is a formula in Column J: =IF(K9="Match","Match", " "). What I would like to do is if "Match" appears , highlight the entire row ( J : A ).
I am trying to have the points and the lines connecting them on my line graph be two different colors depending on a value i put in a column next to the chart values.
For example:
x y Variable
1 5 1
2 10
3 16 1
4 5
The points where there is a 1 in the variable column i would like to make a different color from the other points.
I did an extensive search and all i could find was the following:
Sub myChartClass_Calculate()
Dim cht As Object
Dim p As Object
Dim V As Variant
Dim Counter As Integer
However, this uses the values from the chart data, not an outside variable.
I have DR and CR entries which are laid out. in the traditional T format (under the Ledger tab). I would like to have it formatted to the modern ledger type where there are no left and right but rather laid out in 1 page. The required layout is shown in the Ledger attachment
View 2 Replies View RelatedI need to automate a conditional fill down in excel. I have a sheet, some of the columns have rows of empty cells (the amount of empty cells varies). Whenever there is an empty cell I need to fill down, however when a new value is encountered that must then be filled down and so on. This bit I have as:
Set rngWBSv1 = Range("c2", Range("c65536").End(xlUp))
For Each clWBSv1 In rngWBSv1
If clWBSv1.Value = "" Then clWBSv1.Value = clWBSv1.Offset(-1, 0).Value
Next clWBSv1
Set rngWBS2 = Range("d2", Range("d65536").End(xlUp))
For Each clWBS2 In rngWBS2
If clWBS2.Value = "" Then clWBS2.Value = clWBS2.Offset(-1, 0).Value
Next clWBS2 ......................
I want to include a simple dashboard-light, which turns green, yellow or red, dependent on a value, eg in A1. [like the one appearing after my name]
A1<100 => light turns green
A1 100-500 => light turns yellow
A1 >500 => light turns red
Does something like this exist? Preferably in VBA code?
How to light up a row in excel when working in that row?
I am seeking to insert an image into an Excel cell by a formula or a macro.
I am currently trying to have a green traffic light appear in a cell if another particular cell has the numerical value of "1".
for example, something along the lines of: =if(A1=1,"image1.jpg","").
Now, i know the above formula doesn't work but is something similar possible?