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

View 9 Replies


ADVERTISEMENT

Getting Rid Of Light Blue Lines

Jun 4, 2009

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 Related

Conditional Formate VBA Based On Other Cells

Jul 17, 2007

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

View 9 Replies View Related

Traffic Light Conditional Formatting Based On Text?

Nov 4, 2013

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 Related

Conditional Formatting To Achieve Traffic Light Results For 3 Different Values?

Jan 7, 2014

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

View 5 Replies View Related

Conditional Formatting :: Make Cell Blue After 90 Days Passed

Jan 19, 2010

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 Related

How To Add A Line To The Bottom Of Page For Printing

Apr 8, 2014

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.

View 2 Replies View Related

How To Auto-Fill From Bottom Up

Aug 14, 2008

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

View 9 Replies View Related

VBA - Adding Border Line To Bottom Row Of Set Print Area

Oct 15, 2009

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

View 9 Replies View Related

Reverse Auto-Fill ( Bottom To Top )

Sep 17, 2008

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

View 9 Replies View Related

Top 3 With Dark Colours - Remaining 2 With Light Colours In Conditional Formatting?

May 6, 2014

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

View 1 Replies View Related

Increase Speed Of Macro: Match In Font Color It Will Fill The Top With Orange And Delete The Bottom

Dec 12, 2006

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....................................

View 3 Replies View Related

Macro For Conditional Formatting Bottom Border

Jul 9, 2014

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).

View 7 Replies View Related

Complex Lists (sheet To Calculate Reinforcement Required For Solid Slabs)

Apr 23, 2009

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

View 2 Replies View Related

Retain Text Formate

Jun 11, 2009

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?

View 9 Replies View Related

1800's Date Formate

Aug 13, 2009

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.

View 2 Replies View Related

Replace Date Formate

Jul 7, 2008

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.

View 9 Replies View Related

Conditional Formatting - Change Bottom Sheet Number Color If It Exists On Top Sheet

May 25, 2014

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.

View 8 Replies View Related

Excel 2003 :: Conditional Format Top / Mid / Bottom 33% Of Cells But Ignoring Blank Cells

Mar 25, 2012

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))

View 4 Replies View Related

Show A Custom Date Formate

Feb 8, 2007

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

View 9 Replies View Related

Force TextBox To Be Date FormatE

Apr 30, 2008

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)

View 9 Replies View Related

Conditional Formatting Only Top Line Of Cell

Feb 1, 2010

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.

View 9 Replies View Related

Conditional Duplicate Line Removal

Feb 16, 2009

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 Related

Conditional Formatting An Entire Line

Sep 29, 2009

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 ).

View 9 Replies View Related

Conditional Line Chart Color

Jun 26, 2008

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.

View 4 Replies View Related

DR & CR Match: Formate To The Modern Ledger Type Where There Are No Left And Right But Rather Laid Out In 1 Page

Apr 28, 2006

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 Related

Conditional Fill-Down Until Next Blank Cell

Jan 22, 2009

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 ......................

View 4 Replies View Related

Traffic Light Function?

Jan 10, 2010

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?

View 8 Replies View Related

Automatic Light Up 1 Row When Working In That Row

Apr 9, 2009

How to light up a row in excel when working in that row?

View 9 Replies View Related

Traffic Light In Cell

Nov 22, 2006

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?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved