+ Reply to Thread
Results 1 to 4 of 4

Conditional Formatting: Format Cell if Indicator on first cell in column AND no formula

  1. #1
    Registered User
    Join Date
    06-17-2020
    Location
    Washington, DC
    MS-Off Ver
    Windows 10 Pro
    Posts
    5

    Conditional Formatting: Format Cell if Indicator on first cell in column AND no formula

    Hi All,

    I'm looking for create a conditional formatting rule that formats a cell (fills cell w/ yellow background shading) if two conditions are both met:

    1) in row 1 of whatever column the cell is in, the cell must contain the letter E hard-coded
    2) in the cell to be formatted, the contents must be-hard coded (cannot be a formula)

    I think I might be close to getting it right, but something is missing and I could use the Forum's help. Here is what I've done so far:

    1) created a new conditional formatting rule that applies to the entire worksheet
    2) used this formula to determine which cells to format:

    =AND(RIGHT(A$1,1)="E",CellHasFormula=FALSE, ISNUMBER($1:$1048576))

    3) determined that formula applies to all rows (=$1:$1048576)


    I have attached the workbook I'm using, and if you look at the rules manager, you can see what I've attempted to do. If this formatting rule works as intended, cell I4 should be yellow/blue font (because it's hard-coded and contains an E in cell I1), cell I3 should not be formatted (it contains a formula), and cells H3/H4 should also not be formatted (they do not contain an E in cell H1).

    Any thoughts/assistance are very much appreciated, and I thank you for taking the time to read my question.

    Best,

    RK
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    1,894

    Re: Conditional Formatting: Format Cell if Indicator on first cell in column AND no formul

    I assume you also don't want the first row highlighted (even though the E's are hard-coded. Also, you say "If the cell CONTAINS an E", but your example is only if it ENDS in an E. If both are true, then try this:

    =AND(RIGHT(A$1,1)="E",ISFORMULA(A1)=FALSE,A1<>"",ROW()<>1)

    If it's that the cell in the first row CONTAINS an E, then try this:

    =AND(ISNUMBER(SEARCH("E",A$1)),ISFORMULA(A1)=FALSE,A1<>"",ROW()<>1)

  3. #3
    Registered User
    Join Date
    06-17-2020
    Location
    Washington, DC
    MS-Off Ver
    Windows 10 Pro
    Posts
    5

    Re: Conditional Formatting: Format Cell if Indicator on first cell in column AND no formul

    Gregb11,

    Thank you so much for your prompt/helpful reply. That was exactly what I needed, and now using this I can go and implement other formatting in my sheet. Thank you so much for the explanation! Marking thread as solved.

    Best,

    RK

  4. #4
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    1,894

    Re: Conditional Formatting: Format Cell if Indicator on first cell in column AND no formul

    You're welcome. Glad it worked.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 11-02-2017, 10:54 AM
  2. Replies: 2
    Last Post: 12-05-2014, 07:15 AM
  3. Conditional Formatting - Format row based on cell value in specific column
    By Kite3 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-08-2013, 12:23 AM
  4. Replies: 5
    Last Post: 06-30-2013, 06:54 PM
  5. [SOLVED] Format Cell that hold a formula using a UDF and Conditional formatting
    By CliffB382 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-09-2012, 01:55 PM
  6. Replies: 1
    Last Post: 04-26-2010, 04:53 AM
  7. conditional cell format based on cell in same row, previous column
    By tamiluchi in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-03-2006, 11:15 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.05839 seconds
  • Memory Usage 8,959KB
  • Queries Executed 16 (?)
More Information
Template Usage (34):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (4)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (1)postbit_attachment
  • (4)postbit_legacy
  • (4)postbit_onlinestatus
  • (4)postbit_wrapper
  • (4)showthread_bookmarksite
  • (7)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (50):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_attachment
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1