Results 1 to 10 of 10

Thread: Conditional Formatting Help
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Conditional Formatting Help

    Hi,

    I want to change 4-5 cells black if one of the cells contains the work remitted and if that cell is blank change it red.

    Can anyone help I have tried various ways and can only make the cell with remitted change colour..

    Thanks :-0

    Toni

  2. #2
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,265
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Conditional Formatting Help

    4-5 cells.
    What cell references?

    Do you mean this?
    Change a single cell to black if it contains the word "remitted" but if it is blank change it to red?

  3. #3
    New Member
    Join Date
    Oct 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting Help

    So I want to Change various lines (cells) in columns A-E to Black or Red dependent if Column D contains the word Remitted

    Thanks

  4. #4
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,265
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Conditional Formatting Help

    "Contains the word Remitted"
    Contains or is equal to?
    I'm assuming is equal to.

    Select the area to highlight, e.g. A1:E100

    Conditional Formatting
    New Rule
    Use a formula to determine...

    =D1="Remitted"
    format as black

    =D1=""
    format as red

  5. #5
    New Member
    Join Date
    Oct 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting Help

    I put contained as there is a date next to the word remitted, it has changed them all to red doing the above unless I have done it wrong.

    I am new to this and cannot see how I can attach the spreadsheet to show you?

  6. #6
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,265
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Conditional Formatting Help

    Instead of

    =D1="Remitted"

    try

    =ISNUMBER(SEARCH("Remitted",D1))

  7. #7
    New Member
    Join Date
    Oct 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting Help

    Do I need two rules I have tried with one and it still doesnt change colour...

    =ISNUMBER(SEARCH("",D))
    =ISNUMBER(SEARCH("Remitted",D))

    The spreadsheet is basically

    Column A,B Text, Column C Amount, Column D Text

    So 4 Columns across, 13 lines down.

    It seems to change Column A only when I put that Formula in.

  8. #8
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,265
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Conditional Formatting Help

    Yep 2 rules one for each colour.

    You cant attach files on this forum.
    Upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Conditional Formatting Help

    Assuming your data stats in A2, select A2:E last row & use
    =ISNUMBER(SEARCH("Remitted",$D2))
    and with the same range selected
    =$D2=""
    Last edited by Fluff; Oct 22nd, 2019 at 12:59 PM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,981
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Conditional Formatting Help

    Hi @tonic1972, welcome to the forum"

    Try this

    1. Select the cells you want to format. ... (example: A2:E10)
    2. On the Home tab, in the Styles group, click Conditional formatting > New Rule…
    3. In the New Formatting Rule window, select Use a formula to determine which cells to format.
    4. Enter the formula in the corresponding box.

    =MATCH("*remitted*",$D2,0)

    5. Click the Format… button to choose your custom format. (black color)

    Repeat steps 1 to 4 and add this formula:

    =$D2=""

    5. Click the Format… button to choose your custom format. (red color)
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •