Conditional Formate: Fill Is Light Blue And Bottom Line Solid
Feb 14, 2010
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?
I 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.
I 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.
When 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.
I 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
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).
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
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))
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.
I 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
I 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 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?