Free Microsoft Excel 2013 Quick Reference

Conditional formatting and borders Results

I have read this entire (well almost) forum and have found many near hits,
but nothing is working quite right. I get a report from an outside source
that I must format and distribute. The report is generated using SQL 2003
Reporting Services and it comes in EXCEL but it is formatted 40 ways from
Sunday i.e.;merged cells borders, etc. Here is my dilema: I have rows
containing data in this format

L M N O
3/30 4/16 6/22 7/3
10 3/22 4/12 6/18 7/1
8 4 4 2

This is repeated for many rows (it varies each time the report is run) over
a fixed number of columns. I need it to look like this:

L M N O
11 3/30 4/16 6/22 7/3
12 3/22 4/12 6/18 7/1
13 8 4 4 2

Another twist. I need to run conditional formatting that will color a
particular cell based on its value. I have the formatting worked out, but
only after the data is displayed in separate rows. I hope that this makes
sense. Any help you can provide would be greatly appreciated

Don F.

P.S. If it is possible to apply the formatting to the merged cell without
splitting it, that would be grand!

--
Fishman4

Hi

On a worksheet with premade formulas I used conditional format to mark out
cells with formulas for rows where key kolumn cell (week number - the sring
in format "yyyy.ww") is not empty. The condition p.e. for cell AA126 is set
to
Formula Is =($B1026"")
and when TRUE, then the cell is filled pale blue with dashed borders
(default format is no fill, no borders, color automatic), when not, the cell
looks unformatted like any empty cell. It works excellent.

There was a need for additional conditional format check: the sum of 2
columns values must always equal with value in 3rd column - otherwise there
is some wrong entry on row (p.e. some value of wrong type was copied into
some cell). P.e. for cell AB126 is the conditional format now:
1)
Formula Is =(SUM(AC1026:AD1026)AB1026)
when TRUE, the fill is pale blue, borders are dashed, and font is dark red;
2)
Formula Is =($B1026"")
when TRUE, the fill is pale blue and borders are dashed

This format works, when there is a week number in cell B1026 - when
SUM(AC1026:AD1026)=AB1026, then cell is colored and font is black
(automatic), when the sum doesn't equal with test value, the font is red in
colored cell. But when the cell B1026 is empty, the cell is formatted like
the second condition returned TRUE - it's colored and has dashed border!

For testing I copied the formula from second condition into free cell on
worksheet - the formula returns FALSE. Adding 3rd condition:
Formula Is =($B1026="")
with formats no fill, no borders, automatic color
didn't help either. Has someone any ideas about this?

Thanks in advance!

--
When sending mail, use address arviltarkon.ee
Arvi Laanemets

Cell A1 is a drop down list that you select either "A" or "P". Cell B1 is a
cell where the user enters a number. If A1 is "A" I want the number in B1 to
show as an amount in $ whereas if A1 is "P" i want B1 to show as a percentage.

The conditional formatting option allows me to change fonts, borders, and
patterns but not the number format.

Any help would be appreciated

I want to copy the conditional format of a cell or range of cells, and paste
them to another range of cells, but without changing the default style of the
cell (i.e. I don't want to change the borders)

Any help much appreciated.

Can anyone tell me what things can prevent the Edit Menu>Find feature from
working?

I have an unprotected spreadsheet with a lot of formatting, including
conditional formatting. The data however consists of simple integers with
'general' number formats (the data isn't the product of formulas).

I need to do a find for say, the number 5 with blue font formatting. I have
cleared the Find format before putting blue font formatting in the Find
dialogue.

The Find works for cells with '5' in blue font in some relatively plain
cells, but it doesn't find cells with '5' in blue font which also have a lot
of other formats applied, such as larger fonts, conditional formatting, and
coloured borders.

It doesn't even find '5' in a cell when I have pasted the 'find format'
attributes from that very cell.

What could be preventing Excel from finding the data?
--
Kasama

I have a three column sheet. The first column pulls information from another
sheet. The the other two are going to be blank with underline where someone
will handwrite their initials.

Here's what I wanted to do.

Column A formula:
=IF('sheet1'!A1="","",'sheet1'!A1)

Column A conditional formatting:
Is Formula A2(of the second sheet)>0 or =(A2>0)
The format puts a border on the bottom part of the cell. (one line)

Column's B and C on sheet 2 follow the same kind of conditional formatting.
Basically, if column A contains information, place a bottom border in the
cell (thus creating a blank line). If it doesn't contain any information,
leave the cell blank.

My problem is, if I just apply the conditional formatting to B and C, it
works perfectly. But, when I apply my formula to column A, it puts all the
borders in, and there is NO information (other than formulas) in the A cell.

Conditional formatting is recognizing a formula as information in the cell,
but I don't want it to. What am I doing wrong?

I have a spreadsheet that is calculating intervals for equipment
maintenance. Some of the equipment must be serviced every x months,
some of the equipment must be serviced every x hours (of run time).

I have all the calculations worked out. The problem I am having is with
conditional formatting. In column A I have text that says either
"HOURS" or "MONTHS". Based on the value of that cell, I need Column B
to be formatted as either a Date (to return the date of the next
service) or as a Number (to return the hours till next service).

Any ideas on how to do that? Conditional formatting doesnt give the
option to format anything except Font, Border and Patterns. I need to
format the data type.

Thanks,

Robbie

Hi,

I’m a competent Excel user, but I know only a very little about VBA programming. The difficulty I'm having concerns the conditional formatting function of Excel. A simplified explanation of the problem is I have a spreadsheet where the format of cell A2 depends on the value in the cell A3. I have 10 possible options for the formatting of background/borders. Thus I need more that the 3 (or 4 if you include the base format) conditional format options that excel offers. Is there a piece of VBA code I can paste into the 'View Code' view in my spreadsheet to help sort this? I've searched this web site for an answer to this and although there seem to be many solutions based on the contents of the cell to be conditionally formatted, there are few based on the contents of another cell from that to be formatted. I need multiple options based on the "formula is" rather than the "cell value is" functionality of the conditional formatting tool.

I've attached an example of the problem I have, and showing the 10 possible options I'm after.

Any help would be most greatly appreciated.

Kind regards
Rob

How do I write a Macro for Conditional Formatting code to get Borders?

I want to create a sheet with a Macro and I want to create simple borders around the Cells uptill the last filled cell.

Ex: Data is from Col A till Col K

If there data in the column B then there should be borders around the entire ROW from Col A till Col K.

This whole sheet will be devoid of GridLines and therefore the only borders present will be due to the conditional formatting or if there's any other way of making.

So Any ideas..!

Warm regards
e4excel

Hi guys,

Im using access 2003 and the conditional formatting is only limited to 3. But i wanted 5. I was told it can be done using VBA. But I've got no programming experience whatsover.

Basically this is what i want, but i dont know how to translate this VB

IF Cell Value = >1 then make the value "Bold", "Bordered", and "Fill" with Orange colour.
IF Cell Value = "H" then make the text "Bold", Bordered, and "Fill" with Green colour.
IF Cell Value = "S" then make the text "Bold", Bordered, and "Fill" with Yellow colour
IF Cell Value = "A" then make the text "Bold", Bordered, and "Fill" with Blue colour
IF Cell Value = "U" then make the text "Bold", Bordered, and "Fill" with Red Colour

Thanks in advance to anyone who can help.

Cheers!

How do I get more than three choices for "Conditional Formatting"? I'm not a
coder in any way. I don't want to write a macro (are there any volunteers?).

All I have to do is get the format (font, cell, border) to change when I
type any specific number or name from a list so 1, 2, 3, 4 and 5 are all
different formats and Bill, John, Ted, Trixie, Bubbles and Biff are all
different formats. Those values don't have to relate to each other AT ALL
(names and numbers).

Shouldn't this be easy or do we need an engineering degree to make this
work? I know how to create "Pick From Lists" and I've done "VLookups" (though
that wasn't fun). But I don't understand any of the following terms:

Select Case Me.txtBox
Case "X1", "X2", "X3"
Me.txtBox.BackColor = 255
Case "Y1", "Y2"
Me.txtBox.BackColor = 100
Case "Z1"
Me.txtBox.BackColor = 301
Case "Z2"
Me.txtBox.BackColor = 302
Case Else
Me.txtBox.BackColor = 16777215
End Select

I don't know what any of that means. Not a single word (word?).

Thank you, thank you, thank you if you can put this into terms a simple
artist can understand!!!

Hi,

I have a spreadsheet containing amongst others, 10 lists with conditional
formatting on every cell (e.g. border,pattern etc).

I use VBA code to print these lists but have noticed that when I print more
than 1 sheet, only the first few lines print with the con format displayed
onscreen.

I also noticed that the number of lines printed correctly correspond to the
number of lines in the first sheet i.e. if the first sheet contains 10 lines
and the second contains 20, only 10 lines on the 2nd sheet will print
correctly. If I have more lines on the 1st sheet than the 2nd sheet, all
lines on the 2nd sheet print correctly!

Is this memory related and if so, any suggestions for fixing it?

Thanks for any help.

Lawrence Kritzinger

Cell A1 is a drop down list that you select either "A" or "P". Cell B1 is a
cell where the user enters a number. If A1 is "A" I want the number in B1 to
show as an amount in $ whereas if A1 is "P" i want B1 to show as a percentage.

The conditional formatting option allows me to change fonts, borders, and
patterns but not the number format.

Any help would be appreciated

Hi,

I'm an Excel 2000 (SP-3) user and not much of a coder. I use excel to set up webpages for a little database that I am running. My problem is that I want to use some conditional formatting on the hyperlinks to indicate the status of information in the database. Like this:

Green - all info is there
teal - some info missing
orange - little info available
red - no info

This conditional formatting is used on some large tables of hyperlinks and that works quite well in excel, but when I export it to a web page, then all the conditional colors are completely lost. Only the standard color is there.

Here is an example where the conditinal formatting replaces the green color in excel with red but in the html code it is still just green:

  <td colspan=2 class=xl10020314 style='border-right:.5pt .5pt
.5pt;mso-ignore:
  style;color:red;font-weight:400;text-line-through:none;border-top:none;
  border-right:.5pt solid black;border-bottom:none;border-left:.5pt solid black'><a
  href="Brown.htm#Animal"><span style='color:green;font-family:Arial, sans-serif;
  mso-font-charset:0'>Animal</span></a></td>
The code for red seems to be overwritten by the green. Have I missed something? or forgotten to set something up?

Greetings,

Is there a limit to how much conditional formatting you can do on a page?

I have a matrix that is 110 rows by 60 columns. I have added borders to break the matrix up into 2x2 squares, 1650 boxes total.

I am trying to format thesse boxes so that if a certain condition is met, the upper left and lower right cell of each of these boxes is highlighted (light blue background applied to each cell).

I know how to do this with conditional formatting. However, when I attempted to do this, it seemed to work, but an attempt to save failed due to Excel not being able to save all of my formatting. I clicked OK, and it appeared to save fine, but when I re-opened the document later, only half of the cells contained the conditional formatting. Trying to add the same conditional formatting to the remaining cells resulted in an error message of too large of an area selected.

Is there any other way to apply the formatting so that I can cover the entire matrix?

Can anyone tell me what things can prevent the Edit Menu>Find feature from
working?

I have an unprotected spreadsheet with a lot of formatting, including
conditional formatting. The data however consists of simple integers with
'general' number formats (the data isn't the product of formulas).

I need to do a find for say, the number 5 with blue font formatting. I have
cleared the Find format before putting blue font formatting in the Find
dialogue.

The Find works for cells with '5' in blue font in some relatively plain
cells, but it doesn't find cells with '5' in blue font which also have a lot
of other formats applied, such as larger fonts, conditional formatting, and
coloured borders.

It doesn't even find '5' in a cell when I have pasted the 'find format'
attributes from that very cell.

What could be preventing Excel from finding the data?
--
Kasama

Hi,

Need help with conditional fomatting.

What I need:
I got a worksheet with 950 rows and 5 columns. The first row contain the headers. Columns A, B, D and E contains unique values. But the column C contains text values which repeats sequentially for 6-7 rows and changes thereafter. Now I want to put up a line (using border) through the whole row dividing this transition row.

My Idea:
Compare C2 with C3, if equal do nothing. Then compare C3 with C4 and so on till 2 consequtive values differ. Now divide the 2 rows using the border formatting option in conditional formatting dialog box.

So far...
After selecting the entire worksheet, I have tried each of the below formulas with no desirable result.
=EXACT("C"&ROW(), "C"&ROW()+1) = FALSE
=EXACT(INDEX(C2:C950, ROW(), 3), INDEX(C2:C950, ROW()+1, 3))
=INDEX(C2:C950, ROW(), 3) <> INDEX(C2:C950, ROW()+1, 3)

Is this possible at all? Thanks for any hint/help.

I would like to know how to copy the condition formats that has formula from cell to cell using just VB but not the Cut&Past API.

The problem I ran into was the cell referenced in the formula will not be correctly copied and re-referenced.

for example:

the condition format for cell B1 is:
Formula Is, $A1<>0, then format the text to be green

Now, I like the VB code to copy the same Format condition to B2 which should have the result of:
Formula is, $A2<>0, then format the text to be green.

The problem I ran into is that the "CELL <> 0" was not sometime adjust correctly. for example, if I am copy the B1 condition format to B200, the formula for the condition format is one off as :
Formula is, $A199<>0, then format the text to be green..

Anyhelp will be greatly appreciated. And here is my subroutine:

Private Sub CopyConditionFormats(ByRef rngPref As Range, _
                                 ByRef rngTarget As Range)
    Dim i As Integer
    Dim redo As Boolean
    Dim strFormula As String
    Dim strPrefFormula As String
    Dim strTargetFormula As String

    redo = False

    With rngTarget.FormatConditions
        If .Count < rngPref.FormatConditions.Count Then
            For i = .Count + 1 To rngPref.FormatConditions.Count
                ''Debug.Print "== Formula: " & rngPref.FormatConditions(i).Formula1
                strFormula = Application.ConvertFormula(rngPref.FormatConditions(i).Formula1, xlA1, xlR1C1, , rngPref)

                ''Debug.Print ">> Pref Formula: " & strFormula
                strFormula = Application.ConvertFormula(strFormula, xlR1C1, xlA1, , rngTarget)

                ''Debug.Print ">> Targ Formula: " & strFormula
                .Add Type:=rngPref.FormatConditions(i).Type, _
                    Formula1:=strFormula

                ''Debug.Print ">>"
            Next i
        ElseIf .Count > rngPref.FormatConditions.Count Then
            For i = rngPref.FormatConditions.Count + 1 To .Count
                .Item(i).Delete
            Next i
        End If

        '' may need some optimization here
        ''
        For i = 1 To .Count
            strTargetFormula = Application.ConvertFormula(.Item(i).Formula1, xlA1, xlR1C1, , rngTarget)
            strPrefFormula = Application.ConvertFormula(rngPref.FormatConditions(i).Formula1, xlA1, xlR1C1, , rngPref)

            ''Debug.Print "Targ F: " & strTargetFormula & " vs. " & "Pref F: "
& strPrefFormula
            If .Item(i).Type <> rngPref.FormatConditions(i).Type Or _
               strTargetFormula <> strPrefFormula Then
                redo = True
                Exit For
            End If
        Next i

        If redo Then
            .Delete
            For i = 1 To rngPref.FormatConditions.Count
                strFormula = Application.ConvertFormula(rngPref.FormatConditions(i).Formula1, xlA1, xlR1C1, ,
rngPref.Offset(0, 0))
                strFormula = Application.ConvertFormula(strFormula, xlR1C1, xlA1, , rngTarget.Offset(0, 0))
                .Add Type:=rngPref.FormatConditions(i).Type, Formula1:=strFormula
            Next i
        End If

        For i = 1 To .Count
            Call CpFont(rngPref.FormatConditions(i).Font, .Item(i).Font)
            Call CpCFBorders(rngPref.FormatConditions(i).Borders, .Item(i).Borders)
            Call CpInterior(rngPref.FormatConditions(i).Interior, .Item(i).Interior)
        Next i
    End With
End Sub
Thanks!

---

When I apply conditional formatting to a range of merged cells the
conditional borders I specify do not extend around the entire merged range;
they only appear on the top, left, and bottom of first cell of the range.
Fonts and patterns work fine across the entire merged range, but the borders
do not seem to recognize that the range extends across several cells.

Is thare a workaround for this, or another way to accomplish this without
using code? (The rest of the sheet is working fine, and will be used by a
wide assortment of users so I don't want the risk / hassle of requiring that
macros are enabled to make the sheet work properly).

In case it matters, the conditional formatting is based on a formula such
as: {=A10="OTHER"} so when a value of "Other" is entered in cell A10,
(through a data validation list), the conditionally formatted range should
appear along with a prompt to enter a description of what the value of
"Other" represents.

Thanks, Any suggestions are greatly appreciated,

TK

Hi,

I have searched this forum (and others) for the solution to this problem but without success. I hope somebody can help.

I have been using conditional formatting for a project in Excel 2007 but as the end users are using Excel 2003, I have had to switch to the following VBA solution as my requirements exceed the standard 3 available conditions. I have looked at using custom formatting but I need to format the cell colour rather than just the font colour.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

    If Not Intersect(Target, Range("C19:IV384")) Is Nothing Then
        Select Case Target
            Case "0.5", "1", "U"
                icolor = 38
            Case "C", "M", "P"
                icolor = 40
            Case "A", "S", "D"
                icolor = 36
            Case "L", "UP", "C/E"
                icolor = 35
            Case Else
                'Whatever
        End Select
        
        Target.Interior.ColorIndex = icolor
    End If

End Sub
I am hopeful somebody can help me modify this VBA to achieve the following:

* When the macro is run on one worksheet, formatting and values are replicated simultaneously on another identical worksheet (not necessarily vice versa).

* As well as formatting cell colour when containing a value, a border should also be added with different colours for the top, bottom, left and right border.

* When the cell contains no value, the borders should return to how they were previously.

Many thanks in advance for any advice or solutions.