Hallo, i have a worksheet where i keep track of which jobs i follow on which days in range AO1:XX1 there are the names of the jobs, they are not all there yet, i add them as they are assigned to me...
Does the attached demo return the intended result? The conditional format only applies if the same 'job' occurs at least once in range D5:D370 and in range AO1:XX1. For example "Monday" occurs twice in D5:D370 but not in AO1:XX1 and isn't highlighted.
The download doesn't work unfortunately. Perhaps the suggestion in the attached file works for you. I've made an example for the entries in cells AO1:AQ1 which requires 3 different rules for conditional formatting if you want different colors for all jobs. If there are 608 different jobs in range AO1:XX1 and you want a different color for each job it requires 608 rules for conditional formatting.
I was trying to avoi the need for this many rules, or automate their creation. I'll probably investigate if a vba script can generate a rule when text is added to a cell in a specific interval. Thank you for your help
Resources
"}},"componentScriptGroups({\"componentId\":\"custom.widget.MicrosoftFooter\"})":{"__typename":"ComponentScriptGroups","scriptGroups":{"__typename":"ComponentScriptGroupsDefinition","afterInteractive":{"__typename":"PageScriptGroupDefinition","group":"AFTER_INTERACTIVE","scriptIds":[]},"lazyOnLoad":{"__typename":"PageScriptGroupDefinition","group":"LAZY_ON_LOAD","scriptIds":[]}},"componentScripts":[]},"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"components/community/NavbarDropdownToggle\"]})":[{"__ref":"CachedAsset:text:en_US-components/community/NavbarDropdownToggle-1745505309826"}],"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"shared/client/components/common/QueryHandler\"]})":[{"__ref":"CachedAsset:text:en_US-shared/client/components/common/QueryHandler-1745505309826"}],"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/EscalatedMessageBanner\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/EscalatedMessageBanner-1745505309826"}],"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"components/users/UserLink\"]})":[{"__ref":"CachedAsset:text:en_US-components/users/UserLink-1745505309826"}],"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"shared/client/components/users/UserRank\"]})":[{"__ref":"CachedAsset:text:en_US-shared/client/components/users/UserRank-1745505309826"}],"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageTime\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageTime-1745505309826"}],"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageSolvedBadge\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageSolvedBadge-1745505309826"}],"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageSubject\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageSubject-1745505309826"}],"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageBody\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageBody-1745505309826"}],"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageCustomFields\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageCustomFields-1745505309826"}],"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageReplyButton\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageReplyButton-1745505309826"}],"message({\"id\":\"message:4035039\"})":{"__ref":"ForumTopicMessage:message:4035039"},"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"shared/client/components/users/UserAvatar\"]})":[{"__ref":"CachedAsset:text:en_US-shared/client/components/users/UserAvatar-1745505309826"}],"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"shared/client/components/ranks/UserRankLabel\"]})":[{"__ref":"CachedAsset:text:en_US-shared/client/components/ranks/UserRankLabel-1745505309826"}],"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/AcceptedSolutionButton\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/AcceptedSolutionButton-1745505309826"}],"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/ThreadedReplyList\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/ThreadedReplyList-1745505309826"}],"message({\"id\":\"message:4035098\"})":{"__ref":"ForumReplyMessage:message:4035098"},"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"components/tags/TagView/TagViewChip\"]})":[{"__ref":"CachedAsset:text:en_US-components/tags/TagView/TagViewChip-1745505309826"}],"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"components/attachments/AttachmentView/AttachmentViewChip\"]})":[{"__ref":"CachedAsset:text:en_US-components/attachments/AttachmentView/AttachmentViewChip-1745505309826"}],"cachedText({\"lastModified\":\"1745505309826\",\"locale\":\"en-US\",\"namespaces\":[\"shared/client/components/common/Pager/PagerLoadMore\"]})":[{"__ref":"CachedAsset:text:en_US-shared/client/components/common/Pager/PagerLoadMore-1745505309826"}],"message({\"id\":\"message:4035135\"})":{"__ref":"ForumReplyMessage:message:4035135"},"message({\"id\":\"message:4035835\"})":{"__ref":"ForumReplyMessage:message:4035835"}},"CachedAsset:pages-1745484442433":{"__typename":"CachedAsset","id":"pages-1745484442433","value":[{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"BlogViewAllPostsPage","type":"BLOG","urlPath":"/category/:categoryId/blog/:boardId/all-posts/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"CasePortalPage","type":"CASE_PORTAL","urlPath":"/caseportal","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"CreateGroupHubPage","type":"GROUP_HUB","urlPath":"/groups/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"CaseViewPage","type":"CASE_DETAILS","urlPath":"/case/:caseId/:caseNumber","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"InboxPage","type":"COMMUNITY","urlPath":"/inbox","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"HelpFAQPage","type":"COMMUNITY","urlPath":"/help","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"IdeaMessagePage","type":"IDEA_POST","urlPath":"/idea/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"IdeaViewAllIdeasPage","type":"IDEA","urlPath":"/category/:categoryId/ideas/:boardId/all-ideas/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"LoginPage","type":"USER","urlPath":"/signin","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"BlogPostPage","type":"BLOG","urlPath":"/category/:categoryId/blogs/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"UserBlogPermissions.Page","type":"COMMUNITY","urlPath":"/c/user-blog-permissions/page","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"ThemeEditorPage","type":"COMMUNITY","urlPath":"/designer/themes","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"TkbViewAllArticlesPage","type":"TKB","urlPath":"/category/:categoryId/kb/:boardId/all-articles/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1730819800000,"localOverride":null,"page":{"id":"AllEvents","type":"CUSTOM","urlPath":"/Events","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"OccasionEditPage","type":"EVENT","urlPath":"/event/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"OAuthAuthorizationAllowPage","type":"USER","urlPath":"/auth/authorize/allow","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"PageEditorPage","type":"COMMUNITY","urlPath":"/designer/pages","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"PostPage","type":"COMMUNITY","urlPath":"/category/:categoryId/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"ForumBoardPage","type":"FORUM","urlPath":"/category/:categoryId/discussions/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"TkbBoardPage","type":"TKB","urlPath":"/category/:categoryId/kb/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"EventPostPage","type":"EVENT","urlPath":"/category/:categoryId/events/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"UserBadgesPage","type":"COMMUNITY","urlPath":"/users/:login/:userId/badges","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"GroupHubMembershipAction","type":"GROUP_HUB","urlPath":"/membership/join/:nodeId/:membershipType","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"MaintenancePage","type":"COMMUNITY","urlPath":"/maintenance","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"IdeaReplyPage","type":"IDEA_REPLY","urlPath":"/idea/:boardId/:messageSubject/:messageId/comments/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"UserSettingsPage","type":"USER","urlPath":"/mysettings/:userSettingsTab","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"GroupHubsPage","type":"GROUP_HUB","urlPath":"/groups","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"ForumPostPage","type":"FORUM","urlPath":"/category/:categoryId/discussions/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"OccasionRsvpActionPage","type":"OCCASION","urlPath":"/event/:boardId/:messageSubject/:messageId/rsvp/:responseType","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"VerifyUserEmailPage","type":"USER","urlPath":"/verifyemail/:userId/:verifyEmailToken","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"AllOccasionsPage","type":"OCCASION","urlPath":"/category/:categoryId/events/:boardId/all-events/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"EventBoardPage","type":"EVENT","urlPath":"/category/:categoryId/events/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"TkbReplyPage","type":"TKB_REPLY","urlPath":"/kb/:boardId/:messageSubject/:messageId/comments/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"IdeaBoardPage","type":"IDEA","urlPath":"/category/:categoryId/ideas/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"CommunityGuideLinesPage","type":"COMMUNITY","urlPath":"/communityguidelines","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"CaseCreatePage","type":"SALESFORCE_CASE_CREATION","urlPath":"/caseportal/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"TkbEditPage","type":"TKB","urlPath":"/kb/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"ForgotPasswordPage","type":"USER","urlPath":"/forgotpassword","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"IdeaEditPage","type":"IDEA","urlPath":"/idea/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"TagPage","type":"COMMUNITY","urlPath":"/tag/:tagName","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"BlogBoardPage","type":"BLOG","urlPath":"/category/:categoryId/blog/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"OccasionMessagePage","type":"OCCASION_TOPIC","urlPath":"/event/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"ManageContentPage","type":"COMMUNITY","urlPath":"/managecontent","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"ClosedMembershipNodeNonMembersPage","type":"GROUP_HUB","urlPath":"/closedgroup/:groupHubId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"CommunityPage","type":"COMMUNITY","urlPath":"/","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"ForumMessagePage","type":"FORUM_TOPIC","urlPath":"/discussions/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"IdeaPostPage","type":"IDEA","urlPath":"/category/:categoryId/ideas/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1730819800000,"localOverride":null,"page":{"id":"CommunityHub.Page","type":"CUSTOM","urlPath":"/Directory","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"BlogMessagePage","type":"BLOG_ARTICLE","urlPath":"/blog/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"RegistrationPage","type":"USER","urlPath":"/register","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"EditGroupHubPage","type":"GROUP_HUB","urlPath":"/group/:groupHubId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"ForumEditPage","type":"FORUM","urlPath":"/discussions/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"ResetPasswordPage","type":"USER","urlPath":"/resetpassword/:userId/:resetPasswordToken","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1730819800000,"localOverride":null,"page":{"id":"AllBlogs.Page","type":"CUSTOM","urlPath":"/blogs","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"TkbMessagePage","type":"TKB_ARTICLE","urlPath":"/kb/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"BlogEditPage","type":"BLOG","urlPath":"/blog/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"ManageUsersPage","type":"USER","urlPath":"/users/manage/:tab?/:manageUsersTab?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"ForumReplyPage","type":"FORUM_REPLY","urlPath":"/discussions/:boardId/:messageSubject/:messageId/replies/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"PrivacyPolicyPage","type":"COMMUNITY","urlPath":"/privacypolicy","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"NotificationPage","type":"COMMUNITY","urlPath":"/notifications","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"UserPage","type":"USER","urlPath":"/users/:login/:userId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"OccasionReplyPage","type":"OCCASION_REPLY","urlPath":"/event/:boardId/:messageSubject/:messageId/comments/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"ManageMembersPage","type":"GROUP_HUB","urlPath":"/group/:groupHubId/manage/:tab?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"SearchResultsPage","type":"COMMUNITY","urlPath":"/search","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"BlogReplyPage","type":"BLOG_REPLY","urlPath":"/blog/:boardId/:messageSubject/:messageId/replies/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"GroupHubPage","type":"GROUP_HUB","urlPath":"/group/:groupHubId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"TermsOfServicePage","type":"COMMUNITY","urlPath":"/termsofservice","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"CategoryPage","type":"CATEGORY","urlPath":"/category/:categoryId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"ForumViewAllTopicsPage","type":"FORUM","urlPath":"/category/:categoryId/discussions/:boardId/all-topics/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"TkbPostPage","type":"TKB","urlPath":"/category/:categoryId/kbs/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745484442433,"localOverride":null,"page":{"id":"GroupHubPostPage","type":"GROUP_HUB","urlPath":"/group/:groupHubId/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"}],"localOverride":false},"CachedAsset:text:en_US-components/context/AppContext/AppContextProvider-0":{"__typename":"CachedAsset","id":"text:en_US-components/context/AppContext/AppContextProvider-0","value":{"noCommunity":"Cannot find community","noUser":"Cannot find current user","noNode":"Cannot find node with id {nodeId}","noMessage":"Cannot find message with id {messageId}"},"localOverride":false},"CachedAsset:text:en_US-shared/client/components/common/Loading/LoadingDot-0":{"__typename":"CachedAsset","id":"text:en_US-shared/client/components/common/Loading/LoadingDot-0","value":{"title":"Loading..."},"localOverride":false},"User:user:-1":{"__typename":"User","id":"user:-1","uid":-1,"login":"Deleted","email":"","avatar":null,"rank":null,"kudosWeight":1,"registrationData":{"__typename":"RegistrationData","status":"ANONYMOUS","registrationTime":null,"confirmEmailStatus":false,"registrationAccessLevel":"VIEW","ssoRegistrationFields":[]},"ssoId":null,"profileSettings":{"__typename":"ProfileSettings","dateDisplayStyle":{"__typename":"InheritableStringSettingWithPossibleValues","key":"layout.friendly_dates_enabled","value":"false","localValue":"true","possibleValues":["true","false"]},"dateDisplayFormat":{"__typename":"InheritableStringSetting","key":"layout.format_pattern_date","value":"MMM dd yyyy","localValue":"MM-dd-yyyy"},"language":{"__typename":"InheritableStringSettingWithPossibleValues","key":"profile.language","value":"en-US","localValue":"en","possibleValues":["en-US"]}},"deleted":false},"Theme:customTheme1":{"__typename":"Theme","id":"customTheme1"},"Category:category:microsoft365":{"__typename":"Category","id":"category:microsoft365","entityType":"CATEGORY","displayId":"microsoft365","nodeType":"category","depth":3,"title":"Microsoft 365","shortTitle":"Microsoft 365","parent":{"__ref":"Category:category:products-services"},"categoryPolicies":{"__typename":"CategoryPolicies","canReadNode":{"__typename":"PolicyResult","failureReason":null}}},"Category:category:top":{"__typename":"Category","id":"category:top","displayId":"top","nodeType":"category","depth":0,"title":"Top","entityType":"CATEGORY","shortTitle":"Top"},"Category:category:communities":{"__typename":"Category","id":"category:communities","displayId":"communities","nodeType":"category","depth":1,"parent":{"__ref":"Category:category:top"},"title":"Communities","entityType":"CATEGORY","shortTitle":"Communities"},"Category:category:products-services":{"__typename":"Category","id":"category:products-services","displayId":"products-services","nodeType":"category","depth":2,"parent":{"__ref":"Category:category:communities"},"title":"Products","entityType":"CATEGORY","shortTitle":"Products"},"Forum:board:ExcelGeneral":{"__typename":"Forum","id":"board:ExcelGeneral","entityType":"FORUM","displayId":"ExcelGeneral","nodeType":"board","depth":4,"conversationStyle":"FORUM","title":"Excel","description":"Your community for how-to discussions and sharing best practices on Microsoft Excel. If you’re looking for technical support, please visit Microsoft Support Community.","avatar":null,"profileSettings":{"__typename":"ProfileSettings","language":null},"parent":{"__ref":"Category:category:microsoft365"},"ancestors":{"__typename":"CoreNodeConnection","edges":[{"__typename":"CoreNodeEdge","node":{"__ref":"Community:community:gxcuf89792"}},{"__typename":"CoreNodeEdge","node":{"__ref":"Category:category:communities"}},{"__typename":"CoreNodeEdge","node":{"__ref":"Category:category:products-services"}},{"__typename":"CoreNodeEdge","node":{"__ref":"Category:category:microsoft365"}}]},"userContext":{"__typename":"NodeUserContext","canAddAttachments":false,"canUpdateNode":false,"canPostMessages":false,"isSubscribed":false},"boardPolicies":{"__typename":"BoardPolicies","canPublishArticleOnCreate":{"__typename":"PolicyResult","failureReason":{"__typename":"FailureReason","message":"error.lithium.policies.forums.policy_can_publish_on_create_workflow_action.accessDenied","key":"error.lithium.policies.forums.policy_can_publish_on_create_workflow_action.accessDenied","args":[]}}},"shortTitle":"Excel","repliesProperties":{"__typename":"RepliesProperties","sortOrder":"REVERSE_PUBLISH_TIME","repliesFormat":"threaded"},"tagProperties":{"__typename":"TagNodeProperties","tagsEnabled":{"__typename":"PolicyResult","failureReason":null}},"requireTags":true,"tagType":"PRESET_ONLY","eventPath":"category:microsoft365/category:products-services/category:communities/community:gxcuf89792board:ExcelGeneral/"},"Rank:rank:37":{"__typename":"Rank","id":"rank:37","position":18,"name":"Copper Contributor","color":"333333","icon":null,"rankStyle":"TEXT"},"User:user:2262388":{"__typename":"User","id":"user:2262388","uid":2262388,"login":"FFAMATO","deleted":false,"avatar":{"__typename":"UserAvatar","url":"https://techcommunity.microsoft.com/t5/s/gxcuf89792/m_assets/avatars/default/avatar-1.svg?time=0"},"rank":{"__ref":"Rank:rank:37"},"email":"","messagesCount":4,"biography":null,"topicsCount":1,"kudosReceivedCount":0,"kudosGivenCount":0,"kudosWeight":1,"registrationData":{"__typename":"RegistrationData","status":null,"registrationTime":"2024-01-19T22:30:33.993-08:00","confirmEmailStatus":null},"followersCount":null,"solutionsCount":0,"entityType":"USER","eventPath":"community:gxcuf89792/user:2262388"},"ForumTopicMessage:message:4035039":{"__typename":"ForumTopicMessage","uid":4035039,"subject":"CONDITIONAL FORMATTING BASED ON TEXT","id":"message:4035039","revisionNum":1,"repliesCount":7,"author":{"__ref":"User:user:2262388"},"depth":0,"hasGivenKudo":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"conversation":{"__ref":"Conversation:conversation:4035039"},"readOnly":false,"editFrozen":false,"moderationData":{"__ref":"ModerationData:moderation_data:4035039"},"body@stripHtml({\"truncateLength\":200})":" Hallo, i have a worksheet where i keep track of which jobs i follow on which days in range AO1:XX1 there are the names of the jobs, they are not all there yet, i add them as they are assigned to me...","body@stringLength":"713","rawBody":"
Hallo,
i have a worksheet where i keep track of which jobs i follow on which days
in range AO1:XX1 there are the names of the jobs, they are not all there yet, i add them as they are assigned to me.
in range D5:D370 i input the job i did in that hour
i would like to conditionally format the cells in both ranges so that the same text has the same background color, i dont need to choose which color it is, it should not be very dark tho, and it should not change on every opening of the sheet.
BUT i do not want to manually insert a conditional formatting for each job or even for each cell of the first range.
i have a worksheet where i keep track of which jobs i follow on which days
in range AO1:XX1 there are the names of the jobs, they are not all there yet, i add them as they are assigned to me.
in range D5:D370 i input the job i did in that hour
i would like to conditionally format the cells in both ranges so that the same text has the same background color, i dont need to choose which color it is, it should not be very dark tho, and it should not change on every opening of the sheet.
BUT i do not want to manually insert a conditional formatting for each job or even for each cell of the first range.
Could someone help?
"},"Conversation:conversation:4035039":{"__typename":"Conversation","id":"conversation:4035039","solved":false,"topic":{"__ref":"ForumTopicMessage:message:4035039"},"lastPostingActivityTime":"2024-01-22T04:18:19.628-08:00","lastPostTime":"2024-01-22T04:18:19.628-08:00","unreadReplyCount":7,"isSubscribed":false},"ModerationData:moderation_data:4035039":{"__typename":"ModerationData","id":"moderation_data:4035039","status":"APPROVED","rejectReason":null,"isReportedAbuse":false,"rejectUser":null,"rejectTime":null,"rejectActorType":null},"Revision:revision:4035039_1":{"__typename":"Revision","id":"revision:4035039_1","lastEditTime":"2024-01-19T22:40:54.973-08:00"},"ForumReplyMessage:message:4035103":{"__typename":"ForumReplyMessage","id":"message:4035103","conversation":{"__ref":"Conversation:conversation:4035039"},"author":{"__ref":"User:user:2262388"},"revisionNum":1,"uid":4035103,"depth":2,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"subject":"Re: CONDITIONAL FORMATTING BASED ON TEXT","readOnly":false,"editFrozen":false,"moderationData":{"__ref":"ModerationData:moderation_data:4035103"},"parent":{"__ref":"ForumReplyMessage:message:4035098"},"body":"
Does the attached demo return the intended result? The conditional format only applies if the same 'job' occurs at least once in range D5:D370 and in range AO1:XX1. For example \"Monday\" occurs twice in D5:D370 but not in AO1:XX1 and isn't highlighted.
","body@stringLength":"966","rawBody":"
Does the attached demo return the intended result? The conditional format only applies if the same 'job' occurs at least once in range D5:D370 and in range AO1:XX1. For example \"Monday\" occurs twice in D5:D370 but not in AO1:XX1 and isn't highlighted.
","author":{"__ref":"User:user:1174419"},"isEscalated":null,"postTime":"2024-01-20T01:12:03.635-08:00","parent":{"__ref":"ForumTopicMessage:message:4035039"},"customFields":[],"attachments":{"__typename":"AttachmentConnection","edges":[{"__typename":"AttachmentEdge","cursor":"MjUuMXwyLjF8b3w1fF9OVl98MQ","node":{"__ref":"Attachment:{\"id\":\"attachment:message4035098AttachmentNumber1\",\"url\":\"https://techcommunity.microsoft.com/t5/s/gxcuf89792/attachments/gxcuf89792/ExcelGeneral/218049/1/Conditional%20formatting%20based%20on%20text.xlsx\"}"}}],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}},"repliesCount":3},"Revision:revision:4035103_1":{"__typename":"Revision","id":"revision:4035103_1","lastEditTime":"2024-01-20T02:13:07.065-08:00"},"QueryVariables:ReplyList:message:4035103:1":{"__typename":"QueryVariables","id":"ReplyList:message:4035103:1","value":{"id":"message:4035103","first":10,"sorts":{"postTime":{"direction":"DESC"}},"repliesFirst":3,"repliesFirstDepthThree":1,"repliesSorts":{"postTime":{"direction":"DESC"}},"useAvatar":true,"useAuthorLogin":true,"useAuthorRank":true,"useBody":true,"useKudosCount":true,"useTimeToRead":false,"useMedia":false,"useReadOnlyIcon":false,"useRepliesCount":true,"useSearchSnippet":false,"useAcceptedSolutionButton":true,"useSolvedBadge":false,"useAttachments":false,"attachmentsFirst":5,"useTags":false,"useNodeAncestors":false,"useUserHoverCard":false,"useNodeHoverCard":false,"useModerationStatus":true,"usePreviewSubjectModal":false,"useMessageStatus":true}},"ROOT_MUTATION":{"__typename":"Mutation"},"CachedAsset:text:en_US-shared/client/components/users/UserAvatar-1745505309826":{"__typename":"CachedAsset","id":"text:en_US-shared/client/components/users/UserAvatar-1745505309826","value":{"altText":"{login}'s avatar","altTextGeneric":"User's avatar"},"localOverride":false},"CachedAsset:text:en_US-shared/client/components/ranks/UserRankLabel-1745505309826":{"__typename":"CachedAsset","id":"text:en_US-shared/client/components/ranks/UserRankLabel-1745505309826","value":{"altTitle":"Icon for {rankName} rank"},"localOverride":false},"CachedAsset:text:en_US-components/messages/AcceptedSolutionButton-1745505309826":{"__typename":"CachedAsset","id":"text:en_US-components/messages/AcceptedSolutionButton-1745505309826","value":{"accept":"Mark as Solution","accepted":"Marked as Solution","errorHeader":"Error!","errorAdd":"There was an error marking as solution.","errorRemove":"There was an error unmarking as solution.","solved":"Solved"},"localOverride":false},"CachedAsset:text:en_US-components/messages/ThreadedReplyList-1745505309826":{"__typename":"CachedAsset","id":"text:en_US-components/messages/ThreadedReplyList-1745505309826","value":{"title":"{count, plural, one{# Reply} other{# Replies}}","title@board:BLOG":"{count, plural, one{# Comment} other{# Comments}}","title@board:TKB":"{count, plural, one{# Comment} other{# Comments}}","title@board:IDEA":"{count, plural, one{# Comment} other{# Comments}}","title@board:OCCASION":"{count, plural, one{# Comment} other{# Comments}}","noRepliesTitle":"No Replies","noRepliesTitle@board:BLOG":"No Comments","noRepliesTitle@board:TKB":"No Comments","noRepliesTitle@board:IDEA":"No Comments","noRepliesTitle@board:OCCASION":"No Comments","noRepliesDescription":"Be the first to reply","noRepliesDescription@board:BLOG":"Be the first to comment","noRepliesDescription@board:TKB":"Be the first to comment","noRepliesDescription@board:IDEA":"Be the first to comment","noRepliesDescription@board:OCCASION":"Be the first to comment","messageReadOnlyAlert:BLOG":"Comments have been turned off for this post","messageReadOnlyAlert:TKB":"Comments have been turned off for this article","messageReadOnlyAlert:IDEA":"Comments have been turned off for this idea","messageReadOnlyAlert:FORUM":"Replies have been turned off for this discussion","messageReadOnlyAlert:OCCASION":"Comments have been turned off for this event"},"localOverride":false},"Attachment:{\"id\":\"attachment:message4035098AttachmentNumber1\",\"url\":\"https://techcommunity.microsoft.com/t5/s/gxcuf89792/attachments/gxcuf89792/ExcelGeneral/218049/1/Conditional%20formatting%20based%20on%20text.xlsx\"}":{"__typename":"Attachment","id":"attachment:message4035098AttachmentNumber1","filename":"Conditional formatting based on text.xlsx","filesize":8703,"contentType":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","url":"https://techcommunity.microsoft.com/t5/s/gxcuf89792/attachments/gxcuf89792/ExcelGeneral/218049/1/Conditional%20formatting%20based%20on%20text.xlsx"},"ModerationData:moderation_data:4035135":{"__typename":"ModerationData","id":"moderation_data:4035135","status":"APPROVED","rejectReason":null,"isReportedAbuse":false,"rejectUser":null,"rejectTime":null,"rejectActorType":null},"ForumReplyMessage:message:4035135":{"__typename":"ForumReplyMessage","uid":4035135,"id":"message:4035135","revisionNum":1,"author":{"__ref":"User:user:1174419"},"readOnly":false,"repliesCount":1,"depth":3,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"parent":{"__ref":"ForumReplyMessage:message:4035103"},"conversation":{"__ref":"Conversation:conversation:4035039"},"subject":"Re: CONDITIONAL FORMATTING BASED ON TEXT","moderationData":{"__ref":"ModerationData:moderation_data:4035135"},"body":"
The download doesn't work unfortunately. Perhaps the suggestion in the attached file works for you. I've made an example for the entries in cells AO1:AQ1 which requires 3 different rules for conditional formatting if you want different colors for all jobs. If there are 608 different jobs in range AO1:XX1 and you want a different color for each job it requires 608 rules for conditional formatting.
","body@stripHtml({\"removeProcessingText\":false,\"removeSpoilerMarkup\":false,\"removeTocMarkup\":false,\"truncateLength\":200})@stringLength":"208","kudosSumWeight":0,"postTime":"2024-01-20T05:09:44.000-08:00","lastPublishTime":"2024-01-20T05:09:44.000-08:00","metrics":{"__typename":"MessageMetrics","views":1428},"visibilityScope":"PUBLIC","placeholder":false,"originalMessageForPlaceholder":null,"isEscalated":null,"solution":false,"entityType":"FORUM_REPLY","eventPath":"category:microsoft365/category:products-services/category:communities/community:gxcuf89792board:ExcelGeneral/message:4035039/message:4035135","replies":{"__typename":"MessageConnection","pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null},"edges":[{"__typename":"MessageEdge","cursor":"MjUuMXwyLjF8aXwzfDEzMjowfGludCw0MDM1ODM1LDQwMzU4MzU","node":{"__ref":"ForumReplyMessage:message:4035835"}}]},"customFields":[],"attachments":{"__typename":"AttachmentConnection","edges":[{"__typename":"AttachmentEdge","cursor":"MjUuMXwyLjF8b3w1fF9OVl98MQ","node":{"__ref":"Attachment:{\"id\":\"attachment:message4035135AttachmentNumber1\",\"url\":\"https://techcommunity.microsoft.com/t5/s/gxcuf89792/attachments/gxcuf89792/ExcelGeneral/218057/1/Conditional%20formatting%20based%20on%20text.xlsx\"}"}}],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}}},"ModerationData:moderation_data:4035835":{"__typename":"ModerationData","id":"moderation_data:4035835","status":"APPROVED","rejectReason":null,"isReportedAbuse":false,"rejectUser":null,"rejectTime":null,"rejectActorType":null},"ForumReplyMessage:message:4035835":{"__typename":"ForumReplyMessage","author":{"__ref":"User:user:2262388"},"id":"message:4035835","revisionNum":1,"uid":4035835,"depth":4,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"parent":{"__ref":"ForumReplyMessage:message:4035135"},"conversation":{"__ref":"Conversation:conversation:4035039"},"subject":"Re: CONDITIONAL FORMATTING BASED ON TEXT","moderationData":{"__ref":"ModerationData:moderation_data:4035835"},"body":"I was trying to avoi the need for this many rules, or automate their creation. I'll probably investigate if a vba script can generate a rule when text is added to a cell in a specific interval. Thank you for your help","body@stripHtml({\"removeProcessingText\":false,\"removeSpoilerMarkup\":false,\"removeTocMarkup\":false,\"truncateLength\":200})@stringLength":"203","kudosSumWeight":0,"repliesCount":0,"postTime":"2024-01-22T02:14:10.935-08:00","lastPublishTime":"2024-01-22T02:14:10.935-08:00","metrics":{"__typename":"MessageMetrics","views":1373},"visibilityScope":"PUBLIC","placeholder":false,"originalMessageForPlaceholder":null,"isEscalated":null,"solution":false,"entityType":"FORUM_REPLY","eventPath":"category:microsoft365/category:products-services/category:communities/community:gxcuf89792board:ExcelGeneral/message:4035039/message:4035835","replies":{"__typename":"MessageConnection","pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null},"edges":[]},"customFields":[],"attachments":{"__typename":"AttachmentConnection","edges":[],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}}},"CachedAsset:text:en_US-components/tags/TagView/TagViewChip-1745505309826":{"__typename":"CachedAsset","id":"text:en_US-components/tags/TagView/TagViewChip-1745505309826","value":{"tagLabelName":"Tag name {tagName}"},"localOverride":false},"CachedAsset:text:en_US-components/attachments/AttachmentView/AttachmentViewChip-1745505309826":{"__typename":"CachedAsset","id":"text:en_US-components/attachments/AttachmentView/AttachmentViewChip-1745505309826","value":{"errorTitle":"Failed!","previewFile":"Preview File","downloadFile":"Download File {name}","removeFile":"Remove File {name}","errorBadExtension":"This file does not have a valid extension. \"{extensions}\" are the valid extensions.","errorFileEmpty":"This file is empty or does not exist.","errorTooLarge":"The maximum file size is: {maxFileSize}.","errorTooMany":"Too many attachments. The maximum number of attachments per message is: {maxAttachmentCount, number, integer}.","errorDuplicate":"This file is already attached."},"localOverride":false},"CachedAsset:text:en_US-shared/client/components/common/Pager/PagerLoadMore-1745505309826":{"__typename":"CachedAsset","id":"text:en_US-shared/client/components/common/Pager/PagerLoadMore-1745505309826","value":{"loadMore":"Show More"},"localOverride":false},"Attachment:{\"id\":\"attachment:message4035135AttachmentNumber1\",\"url\":\"https://techcommunity.microsoft.com/t5/s/gxcuf89792/attachments/gxcuf89792/ExcelGeneral/218057/1/Conditional%20formatting%20based%20on%20text.xlsx\"}":{"__typename":"Attachment","id":"attachment:message4035135AttachmentNumber1","filename":"Conditional formatting based on text.xlsx","filesize":8767,"contentType":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","url":"https://techcommunity.microsoft.com/t5/s/gxcuf89792/attachments/gxcuf89792/ExcelGeneral/218057/1/Conditional%20formatting%20based%20on%20text.xlsx"}}}},"page":"/forums/ForumMessagePage/ForumMessagePage","query":{"boardId":"excelgeneral","messageSubject":"conditional-formatting-based-on-text","messageId":"4035039","replyId":"4035103"},"buildId":"HEhyUrv5OXNBIbfCLaOrw","runtimeConfig":{"buildInformationVisible":false,"logLevelApp":"info","logLevelMetrics":"info","openTelemetryClientEnabled":false,"openTelemetryConfigName":"o365","openTelemetryServiceVersion":"25.1.0","openTelemetryUniverse":"prod","openTelemetryCollector":"http://localhost:4318","openTelemetryRouteChangeAllowedTime":"5000","apolloDevToolsEnabled":false,"inboxMuteWipFeatureEnabled":false},"isFallback":false,"isExperimentalCompile":false,"dynamicIds":["./components/seo/QAPageSchema/QAPageSchema.tsx","./components/community/Navbar/NavbarWidget.tsx","./components/community/Breadcrumb/BreadcrumbWidget.tsx","./components/customComponent/CustomComponent/CustomComponent.tsx","./components/messages/TopicWithThreadedReplyListWidget/TopicWithThreadedReplyListWidget.tsx","./components/external/components/ExternalComponent.tsx","./components/messages/MessageView/MessageViewStandard/MessageViewStandard.tsx","../shared/client/components/common/List/UnwrappedList/UnwrappedList.tsx","./components/tags/TagView/TagView.tsx","./components/tags/TagView/TagViewChip/TagViewChip.tsx","./components/attachments/AttachmentView/AttachmentView.tsx","./components/attachments/AttachmentView/AttachmentViewChip/AttachmentViewChip.tsx","../shared/client/components/common/List/UnstyledList/UnstyledList.tsx","./components/messages/MessageView/MessageView.tsx","../shared/client/components/common/Pager/PagerLoadMore/PagerLoadMore.tsx"],"appGip":true,"scriptLoader":[{"id":"analytics","src":"https://techcommunity.microsoft.com/t5/s/gxcuf89792/pagescripts/1730819800000/analytics.js?page.id=ForumReplyPage&entity.id=board%3Aexcelgeneral&entity.id=message%3A4035103","strategy":"afterInteractive"}]}
